Vlookup formula assistance

[Sintek]

Hi All

I have attached a file for your perusal.

My Vlookup formula in Stage 1 document cell D51 keeps throwing a error if the option chosen in cell C50 is "double" anything".

It works fine when "single" or "triple" is chosen.

I'm confused......Please help!!!!!!

[Glenn Kennedy]

So are we!!! Have you attached the correct file?

[Sintek]

Hi Glenn

Oops that might help

[Jonmo1]

You need to use FALSE as the 4th argument of the Vlookup.

OR

Sort the data on the Drop Down Info Sheet
Range B53:I64 in Ascending order by column B.

[Sintek]

Hi Jonmo1

Of course, how silly of me....Brain gets this way after hours and hours of coding and formulas.

Thanks a stack.

[Jonmo1]

You're welcome.

[Sintek]

Hi Jonmo1

Quick question with the same workbook.

Is there a simpler way to express the following formula which works well:

=IF(AND(D50>3.1,D50<=6),VLOOKUP(C50,'DROP DOWN INFO AND PRICING'!B53:I64,3),IF(AND(D50>6.1,D50<=9),VLOOKUP(C50,'DROP DOWN INFO AND PRICING'!B53:I64,4),IF(AND(D50>9.1,D50<=12),VLOOKUP(C50,'DROP DOWN INFO AND PRICING'!B53:I64,5),IF(AND(D50>12.1,D50<=15),VLOOKUP(C50,'DROP DOWN INFO AND PRICING'!B53:I64,6),IF(AND(D50>15.1,D50<=18),VLOOKUP(C50,'DROP DOWN INFO AND PRICING'!B53:I64,7),IF(AND(D50>18.1,D50<=24),VLOOKUP(C50,'DROP DOWN INFO AND PRICING'!B53:I64,8),0))))))

[Jonmo1]

Try

=IFERROR(VLOOKUP(C50,'DROP DOWN INFO AND PRICING'!B53:I64,MATCH(D50,{3.1,6.1,9.1,12.1,15.1,18.1,24.1})+2),0)

[Sintek]

Pure genius Jonmo1

Works like a charm...I'm learning so much from this Forum...You guys are absolutely amazing....
Thanks again