Vlookup with if < >

[Baishamao]

Hello, I am sorry to be so thick about this, but how I can I make my Vlookup choose an alternative column based on less than or greater than?

I need to have column 6 pricing if quantities in column 5 are less than 25, and column 7 if more than 25 to 100, and column 8 if 100 or more.

I have been trying to introduce an additional if statement but I am not succeeding. Any help would be a boon.

kind thanks in anticipation of you Excel-Boffins.


=IF(ISERROR(VLOOKUP(B13,Sheet2!$A$1:$L$10000,6,FALSE)),"",(VLOOKUP(B13,Sheet2!$A$1:$L$10000,6,FALSE))

 

[mikerickson]

Adding a helper column to Sheet2 would be the easiest, but if that isn't possible
=VLOOKUP(B13, Sheet2!$A$1:$L$10000, IF(VLOOKUP(B13,Sheet2!$A$1:$L$1000,5,False),{0,25,100,10000}<25, 6, IF(VLOOKUP(B13,Sheet2!$A$1:$L$1000,5,False),{0,25,100,10000}<100, 7, 8), False)

 

[JMH022]

same principle as if you were only dealing with one sheet...

=IF(F3<25,G3,IF(F3<100,H3,IF(F3>100,I3,"")))

 

[mikerickson]

Try this
=VLOOKUP(B22,Sheet2!$A$1:$L$1000,LOOKUP(VLOOKUP(B22,Sheet2!$A$1:L$1000,5,FALSE),{0,25,100},{6,7,8}),FALSE)

 

[Baishamao]

It still hates me - I am going to try and do an help column on sheet 2 to define the value and do a pick up from there .... I hope.

But you are ever so nice for trying for me.

xx

 

[JMH022]

If you can post some examples of the data or structure of Sheet 1 and Sheet 2, it may be easier to visualize what you are after.

Happy to help.