SOLVED

VLOOKUP Issue, But on one item in the lookup table

Copper Contributor

I am working on refreshing my memory of Excel, as I have been using Libre for a few years now.  I am working on sample data that has a Discount level.  I did some Pivot Tables and decided that I wanted the Discounts to be in order (low to high, not alpha), so I added a Discount Level column and loaded a vlookup formula / chart : 

Discount BandDiscount Level
High3-High
Medium2-Medium
Low1-Low
None0-None

Formula: =+VLOOKUP(E470,VLUPTables!$A$2:$B$5,2)

 

I went back and everything works but Low.  I have looked for extra spaces - beginning and end.  I have checked for 0 vs o, I have retyped Low.  I have copied Low from the table itself.  I have tried using '1-Low.  I thought it would be a row array - but 0-None works!  I must be missing something, but I am at a loss.  I have even deleted and reloaded everything with the same results.  Any help is appreciated.

4 Replies
best response confirmed by mathetes (Silver Contributor)
Solution

@ChReed It seems likely that you want an exact match of your lookup value, so you should change your formula to this:

 

=VLOOKUP(E470,VLUPTables!$A$2:$B$5,2,FALSE)

 

FALSE at the end means that it needs to find an exact match. Without that, you would need to sort your data in ascending order.

@Steve_SumProductCom That worked perfectly! Thank you so much. I thought had sorted the data already, but it was Alpha by Product and then by Discount Band. Not sure why the others worked, but this definitely fixed it. Thank you so much!
I'm glad to hear that helped. Please mark my reply as Answer.
Its already marked at Best Response so someone beat me too it! I thought there might be an "Answer" option, but I can only remove the Best Response notation. This is my first post, so if I am missing how to do this let me know! Thank you again!
1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

@ChReed It seems likely that you want an exact match of your lookup value, so you should change your formula to this:

 

=VLOOKUP(E470,VLUPTables!$A$2:$B$5,2,FALSE)

 

FALSE at the end means that it needs to find an exact match. Without that, you would need to sort your data in ascending order.

View solution in original post