Nested Vlookup and IF function????

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

Can anyone help here?

Regards,
Simon

try this idea

=VLOOKUP(I9,DeductionsAdditions,if(b1>21,5,if(b1>13,4)),,0)

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Simon Lloyd" <Simon.Lloyd.27szey_1147619703.8082@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.27szey_1147619703.8082@excelforum-nospam.com...
>
> Hi all, i am trying to have a Vlookup statement look at a table
> depending on the value in B1, IF I9 =>34 Then IF the value of B1 is =<5
> Look at column 2 ElseIf B1 =>6 AND =<12 Then Look at column 3 ElseIf B1
> =>13 AND =<20 Then Look at column 4 ElseIf B1 =>21 AND =<36 Then Look
> at column 5, I know what i'm trying to get at but lack the knowledge to
> put all the above in to a function in I10, DeductionsAdditions is the
> named range i am looking up from.
> =VLOOKUP(I9,DeductionsAdditions,6,0)
>
> Can anyone help here?
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=541937
>

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

"Simon Lloyd" wrote:

>
> Hi all, i am trying to have a Vlookup statement look at a table
> depending on the value in B1, IF I9 =>34 Then IF the value of B1 is =<5
> Look at column 2 ElseIf B1 =>6 AND =<12 Then Look at column 3 ElseIf B1
> =>13 AND =<20 Then Look at column 4 ElseIf B1 =>21 AND =<36 Then Look
> at column 5, I know what i'm trying to get at but lack the knowledge to
> put all the above in to a function in I10, DeductionsAdditions is the
> named range i am looking up from.
> =VLOOKUP(I9,DeductionsAdditions,6,0)
>
> Can anyone help here?
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=541937
>
>

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

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

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),"")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Simon Lloyd" <Simon.Lloyd.27t1hy_1147622401.5332@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.27t1hy_1147622401.5332@excelforum-nospam.com...
>
> 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
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=541937
>

Surely you can expand on the
try this idea
yourself
--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Simon Lloyd" <Simon.Lloyd.27t11a_1147621802.3393@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.27t11a_1147621802.3393@excelforum-nospam.com...
>
> 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
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=541937
>