VLOOKUP not finding values -- Table Data Issues

meppwc

Well-known Member
Joined
May 16, 2003
Messages
604
Office Version
  1. 365
Platform
  1. Windows
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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))
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Steve............I even tried reformatting them as numbers and that did not work..............Reinput was the only way to get the expected hits.
 
Upvote 0
Sorry..............lots of multi-tasking this morning.............yes, just tried the formula and it worked perfectly............and thank you very much
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top