# VLOOKUP not finding values -- Table Data Issues

#### meppwc

##### Active Member
I have a VLOOKUP issue
The formula is correct
The data that is in column 1 of the table can be a string of numbers, or, letters mixed with numbers.
If I look at a cell in column 1 of the table and then manually type it back into the same cell, then the formula finds the value
This leads me to believe that the data in column 1 of the table has a formatting issue (I guess)
So I have tried to reformat as numbers, text, general and that does not help
I have also tried to clear formatting of all data in that column and that does not help either
But if I put the cursor in front of the first character in one of the cells and press enter, not only does the data move to the right of the cell, it is also found using the formula

I have never run into anything like this before.
Hopefully someone has and knows the fix for it

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### steve the fish

##### Well-known Member
The formula only fails to find numbers then? See if something like this works:

=IFERROR(VLOOKUP(A1,B1:C10,2,0),VLOOKUP(TRIM(A1),B1:C10,2,0))

#### Joe78man

##### New Member
I rather understand what is the issue before throwing solutions, that way I know what I'm doing. If you ctrl+f the value, make sure click on "options >>" and check the box "Match entire cell content". That way you will confirm if the data you are looking for is actually spelled as you see it.
Another attempt should be to copy the value from the lookup value from the cell where it is and paste it in the ctrl-f window. The less you type, the less human errors.
If you confirm the value in the table has a space in the end (invisible but still there) you can use the formula Steve proposed.

I suggest you use index(match()) instead of vlookup. It does the same but you don't need to have the lookup value searched in the first column, you can look in whichever column and bring the value from the column you select. If the table is big it uses less resources because it doesn't need a table, just 2 columns.

=Index(lookup_range,MATCH(lookup_value,range_to_look_for_value,0))
MATCH looks for a value in a range and returns the relative position (number of rows/columns).
INDEX looks in a relative position and brings the value found there.

#### steve the fish

##### Well-known Member
The issue is the numbers are stored as text. Thats why a reinput shifts them to the right. The trim is not to clean off any spurious spaces its to convert the lookup value to textual number.

Last edited:

#### meppwc

##### Active Member

Steve............I even tried reformatting them as numbers and that did not work..............Reinput was the only way to get the expected hits.

#### steve the fish

##### Well-known Member
So you didnt try the formula? Id be surprised if that didnt work.

#### meppwc

##### Active Member
Sorry..............lots of multi-tasking this morning.............yes, just tried the formula and it worked perfectly............and thank you very much

Replies
5
Views
54
Replies
11
Views
153
Replies
0
Views
20
Replies
2
Views
43
Replies
0
Views
40