Index Match Contains?

Art Schneiderheinze

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.

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

SHEET 2
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?

Kelly Moore

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?

Kelly

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/collect