[Solved] If and Vlookup argument in same cell

[Crankey]

Hello All I hope you can help me.

I'm a noob user and trying to put together a simple spreadsheet table as a project to learn how to use a spreadsheet in open office calc (havent used one for 20 years)

Setting the Scene !

Sheet 1. Has a simple lookup table.
This has text entries in column A with a value in column B. the value could be a cost or a payment

Sheet 2. Has my live vlookup calc/working area.

Column A is a date column with each row increasing by 1 day
Column B is a text entry to reflect on this days cost or payment type. This is entered manually and will trigger an appropriate vlookup in C.
Column C is my vlookup data it pulls the value from sheet 1 that matchs the text.
Column D is a running total of the day above + todays new value. So D will grow if a payment is received or reduces if a cost is paid for each day.

Fairly simple and all works OK.

The Problem

What I would like to know is whether or not I can populate the cell in column C with a calculation rather than a simple lookup value when the entry in column B says a particular text entry. e.g. a bonus or a charge depending on the current running balance in D. Otherwise run the standard vlookup.

I have tried combining the IF and Vlookup options in one cell formula but havent managed to get a result

=if(B12;"Charge";sum(D3:D11)*.1;vlookup(b12;Sheet1DataA1:b12,2))

Above is an example or close to it of how I was trying to achieve the result of calc if says "Charge" otherwise give me the vlookup. (Writing this from memory I'm sure the formula is faulty but hope it shows enough to help find a solution)

My thanks in advance

[RusselB]

Your posted formula has an incorrect structure.
From what you have posted, I suspect the formula should look like

Code: Select all

=if(B12="Charge";sum(D3:D11)*.1;vlookup(B12;Sheet1.A1:B12;2))

I don't know where you got the word Data from that shows in your formula, but it occurs to me that you may have renamed the sheet Data, in which case replace Sheet1 in the formula I have suggested with Data
Actually, re-reading your post, makes me think that the vlookup range is specified incorrectly. What makes me think this, is that your formula shows the range being A1:b12, yet you also state that your data is in column C.
A common problem with using Vlookup is that the search column of the data range is unsorted and the 4th parameter is not specified.
With all that I have noticed while writing this and reading and re-reading the post multiple times, I suspect the formula might look like

Code: Select all

=if(B12="Charge";sum(D3:D11)*.1;vlookup(B12;Sheet1.C1:D12;2;0))

The best way to for me to be able to determine just exactly what needs to be changed, I need to see an example of your spreadsheet via an uploaded file.

[Crankey]

Hello Russel many thanks for your reply.

I'm just in from work and will fire up the spreadsheet and apply your logic. Cool that works out fine, in cell 35. my entry wasn't formed properly as I suspected. (I was fairly close though ? lol)

However I given myself 2 new problems. I don;t seem able to copy the new formula to the rows above or below as it causes an ERR entry to appear. This could just be me entering the vlookup part of the formula incorrectly. Also I have now spotted a problem with my simple calc that needs addressing. I have tried applying further logic but failing to get a working solution

Poor Eric will only accrue bank charges on the days he is overdrawn, how would I modify the formula to only take notice of minus Tally values during the previous 7 days and apply his 1% charge.

I'm going to try and attach a freshly made file (Far neater than the one I had been playing with). I've kept the lookup table all on one page.

Cell F35 is the only cell currently with the updated if/vlookup formula but I would like to be able to copy it throughout column F. (I know I will have to modify the formula if there are less than 7 previous days or just tell ;Eric; not to use the entry until 7th January or later)

[RusselB]

One problem with the formula you have in your F35, is that the VLOOKUP can't use F35 as the first parameter in the VLOOKUP, as the formula is in F35.
This creates a circular reference, which is an error.
The easy solution to this, is to change the F35 in the VLOOKUP formula to E35
I know there's a way to have the range adjusted for less than the 7 days if the Charge entry is put in less than 7 days into the budget range, but at the moment the method of doing so escapes me.
You can easily account for the days that Eric is overdrawn by using the SUMIF function, rather than the SUM function that is currently in the formula.
All in, I'd suggest changing your F35 formula to

Code: Select all

=IF(E35="Charge";SUMIF(G28:G34;"<0")*0.01;VLOOKUP(E35;A$2:B$30;2;0))

[Crankey]

Russel you are a star.

You spotted my e35/f35 cell error thankyou and also the sumif works perfectly as a solution for the charge calculation. Not a function I was familiar with on my steep learning curve.

Many thanks and hope you have a good day

Paul.