Using both IF/THEN and VLOOKUP Question

[vince3542]

Hi All-
I've tried searching past threads and I can't find a solution to my issue.

When I input a number in C18, I use VLOOKUP to return data into cell C23. Nice and simple and it works every time.

PROBLEM
I got thrown a kink and I can't figure out how to fix it. Now the value in cell C18 (just numbers 1-4) must be compared against a previously inputted birthdate (age value) to determine what array to use for the VLOOKUP function in C23.

Right now, the formula in C23 reads, =LOOKUP(C18,'Formula Data - Do Not Edit'!A1:A4,'Formula Data - Do Not Edit'!B1:B4) -a nice and simple VLOOKUP that returns a consistent value. This is the formula that needs to be changed to account for the birthdate value being < or > 18 years of age.

Cell F6 contains the birthdate needed to determine what array to use for the VLOOKUP.

I've added a =DATEDIF function in cell F7 to determine if the birthdate is < or > 18 years of age. formula reads =DATEDIF(F6,TODAY(),"y")

Now, when I input a birthdate in F7, cell F8 provides a value (number of years)

I'm wondering how to use the value from F8 to determine what array to use in the VLOOKUP function for cell C23.

If F8>18 then i need to use one set of values in my VLOOKUP. If F8<18 then I need to use a different set of values.

I hope someone can help with this.

 

[p45cal]

Excel Workbook
	A	B	C	D	E
1	2		8		G'bye14
2
3					G'bye14
4
5	1	Hello7
6	2	Hello8
7	3	Hello9
8	4	Hello10		1	G'bye7
9	5	Hello11		2	G'bye8
10	6	Hello12		3	G'bye9
11	7	Hello13		4	G'bye10
12	8	Hello14		5	G'bye11
13	9	Hello15		6	G'bye12
14	10	Hello16		7	G'bye13
15				8	G'bye14
16				9	G'bye15
17				10	G'bye16
Sheet

Either E1 or E3. This uses the value in A1 (not F8 as in your example) = to 1 or not, to decide which array to look at - you get the idea.

 

[vince3542]

you're awesome! can't think you enough for the simplicity and effectiveness of your response.

 

[p45cal]

hi-
you were kind enough to save my bacon yesterday with your handy formula. the only problem is that i colossally screwed up when i replied that it worked perfectly. it only half works perfectly because i screwed up on my description of the problem. I'm not using a VLOOKUP formula, I'm using a LOOKUP formula. - big difference.

when i tried your solution, i could make it work, except for the TRUE/FALSE aspect.

here is the formula i'm using
=IF(F7>18,LOOKUP(C17,'Formula Data - Do Not Edit'!A1:A4,'Formula Data - Do Not Edit'!W1:W4),LOOKUP(C17,'Formula Data - Do Not Edit'!A1:A4,'Formula Data - Do Not Edit'!B1:B4))

I want the cell to return a value between W1 and W4 if F7>18. It works perfectly if F7>18. If F7<18, i need the cell value to be between B1 and B4 and currently that's not happening.

can you tell me what i'm doing wrong? i think the LOOKUP is too limiting. since i'm not using a range of cells, i don't need to use VLOOKUP. that's where i think i'm screwing up.

can you please help me out again?

thanks

It worked here:

Excel Workbook
	A	B	C	D	E	F	G	V	W
1	1	A							F
2	2	B							G
3	3	C							H
4	4	D							I
5
6
7						10			C
8						20			H
17			3
Sheet

Please keep this discussion on-list.

 

[vince3542]

it seems as though i'm still only getting this half correct.

using the following-
=IF(F7>18,LOOKUP($C$17,'Formula Data - Do Not Edit'!$A$1:$A$4,'Formula Data - Do Not Edit'!$W$1:$W$4),LOOKUP(C17,'Formula Data - Do Not Edit'!$A$1:$A$4,'Formula Data - Do Not Edit'!$B$1:$B$4))

works fine when F7 is actually greater than 18.

the problem occurs when F7 contains a number less than 18. if F7 happens to be less than 18, it keeps returning the value in cell W1. i need it to return the value of B1.

is the problem the fact that i'm using another worksheet within the formula?

maybe it's the fact that i'm part moron and i'm missing something completely obvious. i've been trying to modify the formula and i keep failing.

 

[p45cal]

This was in the 'Formula Data -Do Not Edit' sheet:

Excel Workbook
	A	B	W
1	1	A	F
2	2	B	G
3	3	C	H
4	4	D	I
Formula Data - Do Not Edit

This is the other sheet with the value of F7>18:

Excel Workbook
	C	D	E	F	G	V	W
7				20
8
17	3						H
Sheet10

The same sheet with F7<18:

Excel Workbook
	C	D	E	F	G	V	W
7				10
8
17	3						C
Sheet10

The formula in W17 hasn't changed.
Does the above show the results you expect?
The formula can be shorter. This gives the same results:

=LOOKUP($C$17,'Formula Data - Do Not Edit'!$A$1:$A$4,IF(F7>18,'Formula Data - Do Not Edit'!$W$1:$W$4,'Formula Data - Do Not Edit'!$B$1:$B$4))

ps. You haven't got calculation set to manual and forgetting to press F9?

 

[vince3542]

I'm not using manual and forgetting F9. I'm using excel 2004 for mac. maybe this is the problem - i'm modifying your formula on subsequent cells and getting results that are incorrect.

On Cell W17 your formula below returns the result correctly if F7>18 or if F7<18. it's perfect.
=IF(F7>18,LOOKUP($C$17,'Formula Data - Do Not Edit'!$A$1:$A$4,'Formula Data - Do Not Edit'!$W$1:$W$4),LOOKUP(C17,'Formula Data - Do Not Edit'!$A$1:$A$4,'Formula Data - Do Not Edit'!$B$1:$B$4))

If I try to apply the modified formula below to cell W18, the return result is the same as if i left your formula untouched (i.e. did not replace column W with column X), and thus, is returning an incorrect result.
=IF(F7>18,LOOKUP($C$17,'Formula Data - Do Not Edit'!$A$1:$A$4,'Formula Data - Do Not Edit'!$X$1:$X$4),LOOKUP(C17,'Formula Data - Do Not Edit'!$A$1:$A$4,'Formula Data - Do Not Edit'!$B$1:$B$4))

it seems like a natural modification that should work. i feel like such a failure.

 

[p45cal]

Shouldn't both instances of refs to C17 have the same absolute/relative refs?; Either both $C$17, or both $C17, or both C$17, or both C17. If you don't they'll refer to different cells when you copy down/across instead of referring to the same cell (for a given cell's formula).
Pressing F2 when on the cell should highlight the cells it's referring to.

 

[vince3542]

i changed all the C17 cell references within the formula to read $C$17 then tested and got the same results.

i changed all the C17 cell references within the formula to read C17 then tested and got the same results.

i changed all the C17 cell references within the formula to read $C17 then tested and got the same results.

either way i try it, even with the unified references to C17 within the formula, i'm still receiving incorrect results.

unfortunately with a MAC, F2 doesn't shine a light on what cells are being referenced.

it just seems so strange that the formula is working fine in cell W17 and when i modify it to produce the same type of result on W18, i'm failing.

is there anything else i can supply you with to better demonstrate the problem i'm experiencing?

 

[p45cal]

Put the two sheets in a workbook on the interweb somewhere - say box.net?