Help with Index/Match

Brenda Browning

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.

Paul Newcome

Are you able to provide some screenshots for reference?

Brenda Browning

Sure, and thank you!

Screen Shot 2021-10-01 at 7.51.39 PM.png

Screen Shot 2021-10-01 at 7.51.59 PM.png

Screen Shot 2021-10-01 at 7.52.43 PM.png

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.

Paul Newcome

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)

Brenda Browning

This worked!! Thank you so much!

Paul Newcome

Happy to help. 👍️

Brenda Browning

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.

Open Cases Sheet.png

Boarding Pass Sheet.png

Paul Newcome

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?

Brenda Browning

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?

Paul Newcome

Insert a helper column on the Open Case sheet and use

=[Case Number]@row + ""

Then reference this new column in your cross sheet formula.