VLOOKUP issues with concatenated helper cell
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?
Best Answer
-
That would do it. Multi-select columns store a line break after each selection which presents as a space.
Answers
-
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"?
-
Hey @Paul Newcome, I then receive #nomatch
-
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.
-
-
The formulas may match, but the strings they are outputting do not meaning the cell data is not consistent.
First String:
Second String:
-
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.
-
That would do it. Multi-select columns store a line break after each selection which presents as a space.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!