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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!