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 - Cook, Hours - 8am-4pm). I am unable to share the spreadsheet/data due to confidentialty.
The lookup value is from a dropdown box that populates the county in the format of State_County (as there are multiple Jackson counties in different states, for example).
Example 1:
Index Match is looking for info for AK_Anchorage, the results instead pull data for AL_Autauga
Example 2:
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
I pulled a small sample size (20 counties from Alabama) of the data and moved it to an entirely new workbook. The data was copy/pasted into a notepad, then copy/pasted into the new workbook. I did a test VLOOKUP for Autauga, AL and it returned the #N/A error, which it shouldn't as Autauga was in the sample size. I then did a search for Yuba, CA (which should provide an #N/A error since the countie wasn't present in the data), but it instead returned a result for Autauga, AL.
I suspect it has to be a data issue since both the VLOOKUP and Index Match are returning results, but I can't figure out 1) how two search functions using the lookup value could provide different results and 2) how to fix that issue.
Any help would be appreciated
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 errorThat'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.
