Index Match Contains?

In Sheet 1, I have a cell with something like this CAP-400-BL, and I want to search another sheet for an instance of this (there would 0 or 1 instance), but then I want to send back value in a different column in that other sheet.

CAP-400-BL | 202160 <-- This is pulled from Sheet 2, based on CAP-400-BL

CAP-400-BL-10W-Master | Completed | 202160

I am familiar with Index-Match, but what I'm looking for (to "match" with the first column in Sheet 2) won't be a match.

So in Sheet 1, I want to use a formula that looks for the row that contains CAP-400-BL in Sheet 2 , and in Sheet 1 display 202160.

How do I incorporate a "contains" in an Index formula referencing another sheet?

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Art Schneiderheinze

    One approach is to use INDEX/COLLECT. Because I don't know your column names, I will call them CAP columns that contain your CAP text strings, and Number column that contains the 202160 you want returned.

    =IFERROR(INDEX(COLLECT({sheet2 Number column}, {sheet2 CAP column}, CONTAINS([sheet 1 CAP]@row, @cell)), 1),"")

    I wrapped it in the IFERROR so you wouldn't receive an error in the 0 instance cases. As I have it written, it will return a blank if it doesn't find a match. If you wish it to display something else, replace the double quotes at the end with quotes around your message. Example: "my text like this"

    The 1 inside the Index function is the row index number.

    Does this work for you?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!