software

Hi all, i am trying to have a Vlookup statement look at a table
depending on the value in B1, IF I9 =gt;34 Then IF the value of B1 is =lt;5
Look at column 2 ElseIf B1 =gt;6 AND =lt;12 Then Look at column 3 ElseIf B1
=gt;13 AND =lt;20 Then Look at column 4 ElseIf B1 =gt;21 AND =lt;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: www.excelforum.com/member.php...foamp;userid=6708
View this thread: www.excelforum.com/showthread...hreadid=541937try this idea

=VLOOKUP(I9,DeductionsAdditions,if(b1gt;21,5,if(b1gt;1 3,4)),,0)

--
Don Guillett
SalesAid Software

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

A long-winded approach...

=IF(AND(I9gt;=34,B1lt;=5),VLOOKUP(I9,DeductionsAdditio ns,2),IF(AND(I9gt;=34,B1gt;=5,B1lt;=12),VLOOKUP(I9,Deduc tionsAdditions,3),IF(AND(I9gt;=34,B1gt;=13,B1lt;=20),VLO OKUP(I9,DeductionsAdditions,4),IF(AND(I9gt;=34,B1gt;=5 ,B1lt;=12),VLOOKUP(I9,DeductionsAdditions,5),0))))

Regards

Alex

quot;Simon Lloydquot; wrote:

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


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 =gt;x AND lt;=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 lt;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: www.excelforum.com/member.php...foamp;userid=6708
View this thread: www.excelforum.com/showthread...hreadid=541937
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: www.excelforum.com/member.php...foamp;userid=6708
View this thread: www.excelforum.com/showthread...hreadid=541937Can be made shorter if you don't mind creating a little quot;helperquot; 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(I9gt;=34,VLOOKUP(I9,DeductionsAdditions,LOOKUP(B 1,Y1:Z5),0),quot;quot;)

You could also name that range to say quot;listquot;, then:
=IF(I9gt;=34,VLOOKUP(I9,DeductionsAdditions,LOOKUP(B 1,list),0),quot;quot;)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
quot;Simon Lloydquot; gt;
wrote in message
...
gt;
gt; Alex.............it may be long winded but right on the
gt; money........been tearing my hair out with that.....cos' im kinda
gt; stupid really!
gt;
gt; Thanks a lot!
gt;
gt; Regards,
gt;
gt; Simon
gt;
gt;
gt; --
gt; Simon Lloyd
gt; ------------------------------------------------------------------------
gt; Simon Lloyd's Profile:
www.excelforum.com/member.php...foamp;userid=6708
gt; View this thread: www.excelforum.com/showthread...hreadid=541937
gt;Surely you can expand on the
try this idea
yourself
--
Don Guillett
SalesAid Software

quot;Simon Lloydquot; gt;
wrote in message
...
gt;
gt; Don many thanks for the speedy reply, i had a look at your amendment but
gt; its doesn't seem to incorporate all the arguments which will leave a big
gt; hole in my data calculation, the =gt;x AND lt;=x will ensure than it looks
gt; up the value I9 and then the correct column giving the value in I11 as
gt; this VLookup will be in I11. The value in I9 has to be 34 or over
gt; first, if it is then IF the value of B1 is lt;5 then Lookup the value of
gt; I9 in DeductionsAddition and return the corresponding value from column
gt; 2 etc and so on for the rest of the criteria. Maybe its just me not
gt; explaining myself well!
gt;
gt; Hope you can sort this muddles out!
gt;
gt; Simon
gt;
gt;
gt; --
gt; Simon Lloyd
gt; ------------------------------------------------------------------------
gt; Simon Lloyd's Profile:
gt; www.excelforum.com/member.php...foamp;userid=6708
gt; View this thread: www.excelforum.com/showthread...hreadid=541937
gt;