Problem with Vlookup

[56_kruiser]

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?

[BobBridges]

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.

[56_kruiser]

Originally Posted by BobBridges

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.

Thanks so much. Sometimes it takes a new pair of eyes to read the instructions.

I read those several times, and ascending never rang the bell, just sort.

I knew it was something simple.

Thanks again.

[56_kruiser]

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?

[Pecoflyer]

This is giving trouble because the asterisk is a wildcard. Is there a possibility you could change it to something else ? ( like fi / )

[56_kruiser]

Originally Posted by Pecoflyer

This is giving trouble because the asterisk is a wildcard. Is there a possibility you could change it to something else ? ( like fi / )

This is interesting. I'm sure aware of that being a wildcard, but didn't think it would be in this case. But thinking about it, I guess it does make sense, as having it 'search' a table, then I is being used that way.

I may try to discover a way to have it taken literally, but I can use something else if necessary.

Thanks for the input.

[Pecoflyer]

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