VLOOKUP not finding data that is present

[drgnrider]

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?

 

[kweaver]

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.

 

[drgnrider]

To the best of my knowledge, it does not have extra characters. I used TRIM and CLEAN, but not sure if it removed anything.

The data spreadsheet can be found at:

https://www.epa.gov/sites/productio...consolidated_list_of_lists_-_june_2019_0.xlsx

 

[DanteAmor]

You need to put the third column:


=VLOOKUP(C4,'List of Lists by name'!D:F,3,FALSE)

 

[steve the fish]

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.

 

[DanteAmor]

- The "List of Lists by name" worksheet is an EXCEL worksheet downloaded from a U.S. Government website, thus not of my creation.

Review this:

https://www.ablebits.com/office-addins-blog/2016/11/23/remove-spaces-excel/

Especially about char 160

 

[kweaver]

From which sheet are you running this vlookup? Where's C4? None of the sheets have anything in C4.

 

[kweaver]

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.

 

[drgnrider]

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.

 

[DanteAmor]

@DanteAmor: Thank you for the link.

I'm glad to help you. Thanks for the feedback.