Formula for lookup against concatenate
looking to populate a date from another sheet if a match of the unique ID is found on the other sheet.
So sheet A has date column and unique ID column. Sheet B has the same unique ID column but the date is populated in the date column. So I want to bring the date into into Sheet A if a match of the unique ID exists. For further context, when the date column on sheet A is populated with any value, the row will be sent to another sheet, archived basically. I have created the concatenate in it's own column, but I also know that I could do it within the same formula and avoid the extra column.
Not sure if this is just a vlookup or a combination if and match or something?
Tried the following with an incorrect argument error:
=VLOOKUP({unique Id Concat}, [Dist name+Branch Name+Dist Cust Acct #]@row, {Date sent to ITN})
also tried this one with the invalid column value error.
=IF(COUNTIFS({unique Id Concat}, [Dist name+Branch Name+Dist Cust Acct #]@row), {Date sent to ITN}, 0)
Best Answer
-
Once you confirm the INDEX/MATCH is working as expected, you can wrap it in an IFERROR to replace errors with blanks. I don't normally do that right away because it needs to be removed for trouble shooting if it isn't working the first try.
=IFERROR(index/match, "")
Answers
-
Have you looked into an INDEX/MATCH?
=INDEX({Date Column}, MATCH([ID Column]@row, {ID Column}, 0))
-
thanks Paul. Yes that works, but for those that there is no match, it populates "no match" and I need it to be blank until a date is populated so that the automation doesn't pick it up and archive the row. Can it be adjusted slightly?
I was able to accomplish this with Datamesh, but since I'm just helping a different department figure this out, I'd rather not have the datamesh config be only under me. I guess I could transfer ownership of the config, but probably only if they have a license.
-
Once you confirm the INDEX/MATCH is working as expected, you can wrap it in an IFERROR to replace errors with blanks. I don't normally do that right away because it needs to be removed for trouble shooting if it isn't working the first try.
=IFERROR(index/match, "")
-
thanks!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!