VLOOKUP not finding data that is present

drgnrider

New Member
Joined
Aug 17, 2019
Messages
3
Trying to use one worksheet as data and do calculations on another, thus need to pull specific data from the data sheet to my calculations sheet.

Using the formula:
=VLOOKUP(C4&"",'List of Lists by name'!D:D,3,FALSE)

This returns the #NA error, despite my confirming the data is located in row D.

- Both C4 and row D are formatted as text but contain number strings with dashes, (i.e.: 100-41-4).
- It finds the correct data in C4 but not on worksheet "List of Lists by name" in row D. When using the "Show calculation steps", the result of the row D search is shown as 0.
- I added the -&""- after C4 and even tried after D:D to see if it would find the numbers. This suggestion from another page that noted this command has an issue with working with numbers vs. text
- Used this formula on the "List of Lists by name" worksheet, but it also returned #NA , even after replacing -C4&""- with the data copied from the row D cell
- The "List of Lists by name" worksheet is an EXCEL worksheet downloaded from a U.S. Government website, thus not of my creation.

1) Is my formula correct?
2) What could be causing the VLOOKUP and MATCH commands from not finding the data even when searching directly on the worksheet and copying the target data?
3) Is there a limit as to how many rows/cells that VLOOKUP can go through?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Welcome.

You shouldn't need the &"" ... Does your D column on the other sheet have add'l spaces or non-printable characters?

I would probably help if you could post your data somewhere.
 
Last edited:
Upvote 0
You need to put the third column:


=VLOOKUP(C4,'List of Lists by name'!D:F,3,FALSE)
 
Last edited:
Upvote 0
Your formula is wrong but if its showing na error then its not finding the lookup value in column D of the sheet. Use CTRL-F to confirm the value is there. Make sure entire cell contents is checked.
 
Upvote 0
From which sheet are you running this vlookup? Where's C4? None of the sheets have anything in C4.
 
Last edited:
Upvote 0
On the "List..." sheet, I put this in the K6:K1804 column: =trim(clean(D6)) and filled down.

Then, in C4 on that sheet, I entered: 108-05-4 and in D4 I put: =INDEX(F6:F1804,MATCH(C4,K5:K1804,0),1) and found the result of 5,000 without an error.
So, there are probably unprintable characters in your D column that need to be removed to search properly for a CAS code.
 
Last edited:
Upvote 0
Ran TRIM and CLEAN again, no change.
@DanteAmor: Thank you for the link. I was able to confirm there is an extra character on the end, (CHAR says 'ASCII 32'), but was unable to use the...
=TRIM(SUBSTITUTE(SUBSTITUTE(D:D,CHAR(32)," "),CHAR(160)," "))
... to clear this trailing character.

To get the correct data for one line, I deleted the "extra character" and adjusted the formula to:
=VLOOKUP(C4,'List of Lists by name'!D:G,4,FALSE)

Lastly, did a global find/replace on 'ASCII 32' to delete this character, that seems to have fixed the problem.

Thank you everyone for your assistance.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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