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
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