Exact match in vlookup

  • Thread starter Thread starter rmksrv
  • Start date Start date
R

rmksrv

I am using "false" as the 4th parameter in vlookup in order to find a
exact match. What is an exact match in vlookup? I have a value that
think is an exact match but vlookup is returning #N/A.

The exact function is returning TRUE on the 2 values. I used th
format painter to ensure that are not any formatting issues. There ar
no data restrictions. If manually type in the value, then vlookup wil
find it.

Any ideas? This is driving me crazy
 
Your data looks the same but is not. It's likely that Excel is reading one as
text and one as numeric, and the fact that it works when you edit the cell and
hit enter would strengthen that. You cannot just change the format using the
format painter. If the data is supposed to be numeric, but appears to be
textual, try copying an empty cell, select your data and do Edit / Paste Special
/ Add. This should coerce the data back to numeric.
 
Thanks for the response - but that didn't work. The value in questio
is a number as it should be. All I have to do is press F2 on the cel
in question, and then hit enter. Then the vlookup will work.

???
 
Try the following - assuming your data is in say A1, do =ISTEXT(A1) in any other
cell and tell me what the result is (Before you hit F2 on A1). If it says
FALSE, then hit F2 and tell me what it says then.
 
I have run into this before where the data types are not the same. If you are doing a lookup of numeric fields, be sure both are the lookup and array fields are numberic (or both text). You can do this with the formula Value(text) ...... converts a text string that reproesents a number to a numeric field. (or see the TEXT formula if this applies in your case.)
 
Is Sept a range name, and if so, then why would you be looking to return data
from Column 1?? What is in A2
 
yes - sept is a range name. i was returning position 1 just as a test.
Position 2 also does not work.

it doesn't work regardless if I use a range name or specify exactly the
range.

A2 contains a number that idenitifies a financial security. Column 2
in the vlookup range would be the value of the security.
 
OK - are you able to send me the file so I could take a look?? You would need
to take the nospam out of my email address.
 
Found a solution, not sure I understand why but.....

I used the data, 'convert text to columns' tool. I pasted the values
(in the array) over the top of themselves with a General format.

Its confusing, the 'istext' function returned false so I thought these
were numbers to begin with.


Thanks for your time.
 
Back
Top