IF/THEN VLOOKUP Question

August 10, 2011 at 14:26:30
Specs: Macintosh
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.

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.


See More: IF/THEN VLOOKUP Question

Report •

#1
August 10, 2011 at 15:41:51
Before we go any further, explain this:

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.

The formula shown uses the LOOKUP function, not VLOOKUP.

VLOOKUP and LOOKUP are not the same function. Please explain why you say that this is a "nice and simple VLOOKUP".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 10, 2011 at 15:45:30
a user named "p45cal" submitted the following fix. consider this post SOLVED

Re: Using both IF/THEN and VLOOKUP Question
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

Spreadsheet Formulas
Cell Formula
E1 =IF(A1=1,VLOOKUP(C1,$A$5:$B$14,2,FALSE),VLOOKUP(C1,$D$8:$E$17,2,FALSE))
E3 =VLOOKUP(C1,IF(A1=1,A5:B14,D8:E17),2,FALSE)


Report •

#3
August 10, 2011 at 16:18:20
Where was this "fix" submitted? I don't see it in this thread.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question