Help with Index/Match
Hello! I am trying to put together a formula that I could use some help on. I have 2 different sheets, Boarding Passes and Open Cases. In Open Cases, I have a Case Number. In Boarding Passes, I have a Case Number(s) field that could reference more than one of the case numbers in Open Cases (these should be separated by a comma).
I am trying to build a formula that will look for the Case Number from Open Cases within the Case Number(s) field in the Boarding Passes sheet and return a field to me. I have tried different versions of IF, CONTAINS, VLOOKUP, INDEX/MATCH. Below is the closest I was able to get without causing an error, but it is still not matching correctly to bring the value I need.
=IFERROR(INDEX({Boarding Pass Requests Range 1}, MATCH([Case Number]@row, {Boarding Pass Requests Range 1}, 0), 45), "")
I need to be able to do a wildcard match of some kind. Is that possible?
Any help would be appreciated.
Answers
-
Are you able to provide some screenshots for reference?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Sure, and thank you!
Attached is the Boarding Pass document, along with a visual of the Case Numbers that are separated with a comma.
On the second image is the Open Case document, with the single Case Number field.
I am trying to find the Case Number in Open Cases within the Case Numbers in the Boarding Pass field and return the BP ID.
-
Ok. You are going to want something along these lines:
=INDEX(COLLECT({Boarding Passes BP ID Column}, {Boarding Passes Case Number Column}, CONTAINS([Case Number]@row, @cell)), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This worked!! Thank you so much!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul. Actually, I have noticed that the formula works great when multiple Case Number(s) are referenced in the Boarding Pass sheet, but does not work when there is only a single Case Number.
I've attached a few screenshots highlighting the cells that are not being recognized.
-
See how in the Open Cases sheet the case numbers are right justified, but in the Boarding passes sheet the case numbers are left justified? Unless you have formatted the columns this way, this means that they are numerical values in the open cases sheet but text values in the boarding passes sheet.
How exactly are they being populated in each of the sheets?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I do not recall if I changed the justification on either sheet. The Open Case sheet is populated by a copy/paste from Excel, and the Boarding Pass sheet is populated from a form.
The only rows that worked were the rows that had commas in Cases Number(s) in Boarding Pass. Is there anything I can do to have them all be text?
-
Insert a helper column on the Open Case sheet and use
=[Case Number]@row + ""
Then reference this new column in your cross sheet formula.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!