Help with VLOOKUP

[bobbied]

I have created a mock quote page and am wondering if it is possible to create a formula with VLOOKUP to look for more than one price based on one product code. In other words, if I enter product code 1111101, I would like it to show me the appropriate price for that product plus the next size up, which would be 1111105.

I am including an example spreadsheet to hopefully better explain what I am attempting.

Thanks,

[Ron Coderre]

I *think* this is what you're looking for...

Using your posted workbook:
If there is an "05" version of the current Product Code, this formula returns the price for that version:

Please Login or Register  to view this content.

Is that something you can work with?

BTW...You've got a leading space in your tab name: " Pricing" vs "Pricing". If that is not intentional, I recommend correcting it.

[Cutter]

This formula will return what you want for the Quote Price:

Place it in cell K20 and drag up/down:

=INDEX(' Pricing'!$A$9:$D$18,MATCH('Quote Sheet'!A20,' Pricing'!$A$9:$A$18,0)+1,4)


EDIT: sorry, misread - this only works for the PC ending in 1 - would need IF() to check for last digit and use -1 adapting for PC ending in 5

[Cutter]

Corrected formulas:

In I20 and dragged up/down:

Please Login or Register  to view this content.

In K20 and dragged up/down:

Please Login or Register  to view this content.

[Cutter]

And this formula in B20 (dragged up/down) should take care of your other request:

Please Login or Register  to view this content.

I've got a BTW for you, too. The numbers in your pricing table make no sense. Why are the smaller samples often way more than the larger ones??

[bobbied]

Thanks for your replies. Ron your formula works great on the spreadsheet I used as an example, but when I try to copy your formula in to my real price sheet the VLOOKUP will not recognise the answer in the cell that has removed the 01 and replaced it with an 05. If I simply type in the product code the VLOOKUP will work. I hope this makes sense, as I am baffled.

Yes, I know there is a space in front of pricing on the tab, but I didn't worry about it as this was an example. I also wasn't worried about the pricing matching sizes as I just needed to demonstrate what I was trying to accomplish.

Thanks again for your comments, they are very much appreciated.

[bobbied]

Sorry, false alarm. I am not sure what I was doing wrong before but I tried one more time and got it to work this time.

Thanks

[Ron Coderre]

Glad to hear it...thanks for the update.