# Thread: Vlookup function (Excel 2000)

1. ## Vlookup function (Excel 2000)

Would someone mind looking at the attached spreadsheet and telling me where I'm going wrong? I've spent WAY too much time trying to figure the problem out today and I still don't have the answer. This is a sales order spreadsheet. The relevant inputs are 1) pricing level (AR2), 2) quantity, 3) unit of order (cases or pallets) and 4) and product (one of four choices based on package size and labels). The formula I'm having problems with is in the Unit Price column. It uses some tables on the Pricing Table tab to determine the proper unit price which is then multiplied by the quantity to determine the total price for the line item. Everything seems to work fine for the first three selections from the drop down menu in the pricing level box. However, when I choose the fourth option (12 pallets), the Unit Price formula returns #N/A, which apparently means it can't locate that value in the lookup table. However, I have verified that the value is indeed there. What am I missing? I protected the worksheet without a password in case you need to look at any of the locked cells. Many thanks.

Phil

2. ## Re: Vlookup function (Excel 2000)

Further to Legare's post...

If you put the "Pricing Table A16:B19" in ascending order then your formula works.
As follows:

12 Pallets 5
3 Pallets 2
3-6 Pallets 3
6-12 Pallets 4

Regards,

Jim Cone
San Francisco, CA

3. ## Re: Vlookup function (Excel 2000)

Legare and Jim, thanks for your help.

Phil

4. ## Re: Vlookup function (Excel 2000)

Changing your formula to the below seems to work:

<pre>=VLOOKUP(F24&VLOOKUP(L24,'Pricing Table'!A\$11:B\$14,2,FALSE),'Pricing Table'!A\$5:E\$8,VLOOKUP(AR\$2,'Pricing Table'!A\$16:B\$19,2,FALSE),FALSE)
</pre>

I'm not sure what the LOOKUP function is doing, but it appears that it is doing an approximate match that does not work on the last entry in the tables.

Edited by Legare Coleman to change that last TRUE to FALSE so that the original VLOOKUP does and exact match.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•