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.

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.

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'bye16Spreadsheet 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)

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.

Ask Your Question

Weekly Poll