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