VLOOKUP not finding values -- Table Data Issues

meppwc

Active Member
Joined
May 16, 2003
Messages
423
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
 

Some videos you may like

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
Joined
Oct 20, 2009
Messages
8,103
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 26, 2018
Messages
15
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
Joined
Oct 20, 2009
Messages
8,103
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 16, 2003
Messages
423

ADVERTISEMENT

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
Joined
Oct 20, 2009
Messages
8,103
Office Version
  1. 365
Platform
  1. Windows
So you didnt try the formula? Id be surprised if that didnt work.
 

meppwc

Active Member
Joined
May 16, 2003
Messages
423
Sorry..............lots of multi-tasking this morning.............yes, just tried the formula and it worked perfectly............and thank you very much
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,313
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top