Forum Discussion
VLOOKUP / Index Match returning different AND incorrect data
My VLOOKUP and Index Match functions are pulling different data, and both are incorrect data (not errors). This is a spreadsheet that has State and County data (example: State - Illinois, County - Co...
That is a nice combination of bad data and bad formula.
VLOOKUP is looking for info for AK_Anchorage, the results instead pull data for WI_Wood. Please note, data only pulls when using TRUE. Using FALSE gives the #N/A error
That's the bad formula. VLOOKUP() needs the FALSE in 4th argument.
The bad data probably has leading/trailing spaces.
- =VLOOKUP(H1,BIC!B2:F3623,2, TRUE) is the formula I have, if I put TRUE it returns a result, if I put FALSE it doesn't return anything. Can you help explain what's wrong with the formula? It appears to be in the 4th argument (unless i am not understanding, i am not an expert)
Any idea how to fix the bad data in bulk? There's way too much data to go in cell by cell*
=VLOOKUP(H1,BIC!B2:F3623,2, TRUE)That's bad formula #2. The lookup range should have absolute references.
https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
For the bad data try TRIM() - either in the formula or in a helper column. I suggest helper column first.
- =VLOOKUP(H1,BIC!$B$2:$F$3623,1, FALSE)
When using this formula, I still get #N/A but if I switch to TRUE, I get the same result as not having absolute references.
The bad data TRIM() function I will have to dig into as I am completely unfamiliar with that
