Help with Index/Match

09/29/21
Answered - Pending Review

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Are you able to provide some screenshots for reference?

    thinkspi.com

  • 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.

  • Paul NewcomePaul 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)

    thinkspi.com

  • This worked!! Thank you so much!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • 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.


  • Paul NewcomePaul 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?

    thinkspi.com

  • 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 NewcomePaul 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.

    thinkspi.com

Sign In or Register to comment.