VLookup on text values

[aivoryuk]

Hi I am trying to perform standard vlookup formula
=VLOOKUP(Q13,Sheet4!A896:J914,6,FALSE) but I keep getting a #N/A error message and it keeps saying it is becuase the are text values.
The value I am referencing is in Text reference (it's quite long so doesn't work as a general number) and it is store on sheet4 as well as a text format.

How can I get this work?

 

[Joe4]

What it essentially telling you is that it cannot find a match. This can be because:

1. You really have no matching value in your vlookup table.

2. You are trying to look up a text entry in a numeric list, or vice versa. It is not enough to look at the cell format, you need to look at the actual content in the cell. A good way to tell is to use the ISNUMBER function on each half of the match, and see if it returns the same value in each instance.

3. Related to item #1, you have two entries which "appear" to match, but really don't (maybe there is an extra space in the beginning or at the end). You can use the LEN function on each half of the match to see if the entries are the same length. If they are not, they will not create a match.

 

[aivoryuk]

Hi I have checked everything you have mentioned and everything matches and shows that there they are text values so I am not sure what to try next

 

[Joe4]

What cell on Sheet4 has the corresponding matching value for Q13?
Let's say it is cell A995.

They on the sheet with Q13, enter this formula somewhere.
=Q13=Sheet4!A995

If that does not return TRUE, then those two entries are really NOT equal (regardless of how it may look!).

Did any of this data happen to come from the web, or some sort of imported file, perchance?

 

[aivoryuk]

Hi

I tried what you suggested and it did come back with true, I think the original file was imported.

I have come up with a solution of just pasting the information and sorting out the format and seems to work but am curious to know of there is away to do it within the formula

 

[Joe4]

If that formula comes back as True, then the VLOOKUP for that particular value in Q13 should work. Was that one of the cells you were getting an error on, or what is a different one?

If it was a different one, try the steps I gave you on that particular example.