Nested Vlookup and IF function????


S

Simon Lloyd

Hi all, i am trying to have a Vlookup statement look at a tabl
depending on the value in B1, IF I9 =>34 Then IF the value of B1 is =<
Look at column 2 ElseIf B1 =>6 AND =<12 Then Look at column 3 ElseIf B
=>13 AND =<20 Then Look at column 4 ElseIf B1 =>21 AND =<36 Then Loo
at column 5, I know what i'm trying to get at but lack the knowledge t
put all the above in to a function in I10, DeductionsAdditions is th
named range i am looking up from.
=VLOOKUP(I9,DeductionsAdditions,6,0)

Can anyone help here?

Regards,
Simo
 
Ad

Advertisements

G

Guest

Simon

A long-winded approach...

=IF(AND(I9>=34,B1<=5),VLOOKUP(I9,DeductionsAdditions,2),IF(AND(I9>=34,B1>=5,B1<=12),VLOOKUP(I9,DeductionsAdditions,3),IF(AND(I9>=34,B1>=13,B1<=20),VLOOKUP(I9,DeductionsAdditions,4),IF(AND(I9>=34,B1>=5,B1<=12),VLOOKUP(I9,DeductionsAdditions,5),0))))

Regards

Alex
 
S

Simon Lloyd

Don many thanks for the speedy reply, i had a look at your amendment but
its doesn't seem to incorporate all the arguments which will leave a big
hole in my data calculation, the =>x AND <=x will ensure than it looks
up the value I9 and then the correct column giving the value in I11 as
this VLookup will be in I11. The value in I9 has to be 34 or over
first, if it is then IF the value of B1 is <5 then Lookup the value of
I9 in DeductionsAddition and return the corresponding value from column
2 etc and so on for the rest of the criteria. Maybe its just me not
explaining myself well!

Hope you can sort this muddles out!

Simon
 
S

Simon Lloyd

Alex.............it may be long winded but right on the
money........been tearing my hair out with that.....cos' im kinda
stupid really!

Thanks a lot!

Regards,

Simon
 
R

Ragdyer

Can be made shorter if you don't mind creating a little "helper" range to
determine the column to return.
If you put this in an out-of-the-way area of your sheet, say Y1 to Z5:
Y - Z
0 2
6 3
13 4
21 5
37 6

Then the formula could be:

=IF(I9>=34,VLOOKUP(I9,DeductionsAdditions,LOOKUP(B1,Y1:Z5),0),"")

You could also name that range to say "list", then:
=IF(I9>=34,VLOOKUP(I9,DeductionsAdditions,LOOKUP(B1,list),0),"")
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top