#1
|
|||
|
|||
Problem with Vlookup
I am trying to do a non-exact vlookup and having strange results.
I have a cell that has a calculated number, formatted as percentage. When I lookup that percentage in a table, it is returning N/A. Doing some testing with value in a formula , I can try different exact values, and some return a value, and others don't. No non-exact values return anything. I have attached an example spreadsheet. Any ideas what I'm doing wrong? |
#2
|
||||
|
||||
I hardly ever use the True option for the fourth VLOOKUP argument, kruiser; almost always—well, always that I can remember—I'm looking for exact value, so I plug in 0 there. ("0" takes up less space than False, but means the same thing.)
So I had to look up what happens when you use True, and it says the table you're looking at—Table4, in C16 in your sample—must be in ascending order. You have it in descending. I'm guessing that Excel reasons this way: Starts at S4. The value in S4 is 100". The value of C15 is 60.5. Is C15 greater than S4? No, it's less. Well, that's it; since all the rest of the values in Table4 must be 100 or greater, then clearly no value in Table4 will fit the search. So it returns #N/A. |
#3
|
|||
|
|||
Quote:
I read those several times, and ascending never rang the bell, just sort. I knew it was something simple. Thanks again. |
#4
|
|||
|
|||
Now exact match not working...
I am uploading the example spreadsheet with a different issue noted in it.
I have changed a lookup to include the Lookup Argument of false, which means I want an exact match only in the lookup. But it is working as expected. I have the falue of M* in the cell that is being looked up. But it is returning the value for M. What I think is happening is it is ignoring the *. I don't want it to do that. The spreadsheet example is attached to this post. Any thoughts? |
#5
|
||||
|
||||
This is giving trouble because the asterisk is a wildcard. Is there a possibility you could change it to something else ? ( like fi / )
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
|||
|
|||
Quote:
I may try to discover a way to have it taken literally, but I can use something else if necessary. Thanks for the input. |
#7
|
||||
|
||||
The only way to look at it literally (AFAIK) is to preced it with a tile ~*.
But in this case it desn't seem practical
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Question: Will Vlookup work on this problem? | jnix612 | Excel | 1 | 07-27-2013 12:11 AM |
VLookup formula Problem (salary plus (commission x sales)) | topgear2015 | Excel | 11 | 06-05-2013 09:02 PM |
Can i do this with a VLookup? | foodstudent | Excel | 1 | 01-21-2011 12:34 AM |
Using IF & VLOOKUP together | junction | Excel | 7 | 11-18-2010 05:15 AM |
Vlookup and If statement problem | bunnygum | Excel | 1 | 03-24-2009 05:10 AM |