VLOOKUP issues with concatenated helper cell

jcouncil

I have a tracker sheet that has manual entries across three inputs: LOB, Data Type & Source. Those three inputs pull into separate columns. I then have a helper column on this tracker that pulls together those three elements. I want to use this helper cell to be the look up value in my VLOOKUP to another sheet that has the same three data points also concatenated, to then return a value in a different column. It is not pulling the exact match and I'm not sure if that has something to do with the formatting of the helper cell.

Tracker Sheet:

Data Source sheet:

The yellow highlights indicate the values that are replicated across sheets, and the blue highlighting indicates the values I want to pull forward as the result of my vlookup. So, in the tracker sheet, I have column "Regulation" and the VLOOKUP formula =IFERROR(VLOOKUP([LOB & Data Type & Source]@row, {Data Sources_Range1}, 7, false), " ").

If I remove the IFERROR piece, and change false to true, it returns the regulation data for a row that does not match my LOB+ data type + source row on the data source sheet.

Any suggestions?

Paul Newcome

That would do it. Multi-select columns store a line break after each selection which presents as a space.

Paul Newcome

You say you are getting an incorrect match when you remove the IFERROR and use "true", but what happens when you remove the IFERROR and leave it as "false"?

jcouncil

Hey @Paul Newcome, I then receive #nomatch

Paul Newcome

Ah. I think I see the problem and sorry for missing it the first time...

It looks like you don't have the same spacing on either side of the hyphens.

First screenshot:

First hyphen = word space hyphen space word

Second hyphen = word hyphen space word

Second screenshot

First & Second hyphens = word hyphen word hyphen word

Double check that your strings are actually matching.

jcouncil

@Paul Newcome they appear to match.

Data Sources sheets formula:

Data Usage sheet formula:

Paul Newcome

The formulas may match, but the strings they are outputting do not meaning the cell data is not consistent.

First String:

Second String:

jcouncil

I was able to figure out that the issue was stemming from the column type of the columns feeding my helper cell. Since those columns were multi-select, it was creating extra spaces when pulling through formula. When I changed those columns to not allow multi select, the formulas operate as expected. Will put in a ticket to have that glitch fixed hopefully.

Paul Newcome

That would do it. Multi-select columns store a line break after each selection which presents as a space.