CODEWITHSUNDEEP
excelvlookupexcel-2013
Ben Hamilton
Ben Hamilton

Reputation: 975

Excel VLOOKUP - not detecting matches

I am trying to use a VLOOKUP in the XL worksheet linked below. I am trying to lookup based on a text string (9 digits numbers and sometimes includes an X), it looks like it has worked OK but it has not picked up all the cases.

Here is the formula :-

=VLOOKUP(A2, Lookup!$A$2:$B$8845, 2, FALSE)

The lookup data itself is in the second tab, called 'Lookup'.

There are some cases where the formula returns "#N/A", as if the match cannot be found in the lookup list, but where in fact there is a match in the list e.g. 300431419 (row 27 in the main data sheet). I've checked for extra spaces or anything else that would prohibit a match but cannot see anything.

I am using the FALSE flag in the formula which I understood to mean an exact match is needed and the lookup data did not need sorting. Both columns have been set to 'Text' format.

Can anyone help?

https://drive.google.com/file/d/0B0IxepT0FbEFMGlCYWM5dHl4Rm8/view?usp=sharing

Upvotes: -1

Views: 17510

Answers (2)

Ahmedakhtar11
Ahmedakhtar11

Reputation: 1458

Scenario 1: VLOOKUP Not Detecting Text Matches

Problem: All or some of the cells in either of the corresponding columns aren't being recognized as a Text field/cell. Solution: Use the =TRIM formula on both corresponding columns (and then remove formulas) to make sure all cells in both corresponding columns are text fields.

Scenario 2: VLOOKUP Not Detecting Integer/Number Matches

Problem: All or some of the cells in either of the corresponding columns aren't being recognized as an Integer/Number field/cell. Solution: Multiply both corresponding columns by * 1 (and then remove formulas) to make sure all cells in both corresponding columns are Integer/Number fields.

Scenario 3: VLOOKUP Not Detecting Anything

Problem: Data in Excel files may not be getting recognized at all in some cases, especially in cases where the file was converted from an image to a spreadsheet for example. Solution: Copy and paste the contents from the Excel sheet into a Microsoft Word Document. (.TXT files won't work) (Save and close and then open the Word File as an extra precaution.) Then copy and paste the data from the Word File back into a new excel sheet.

Tip: A green triangle on the top right of a cell indicates that it is being recognized as a text field/cell.

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53135

Some data in your "Main Data" sheet are numbers, all data in your "Lookup"sheet are strings. As pnuts said, Text and Numbers don't match.

To fix, convert your "Main Data" sheet data to text, like this

=VLOOKUP(TEXT(A2,"@"), Lookup!$A$2:$B$8845, 2, FALSE)

Upvotes: 3

Related Questions