Formula help with Index Match and Contains

Trish Dillon

Here is what i have in 2 ways but neither work:

IF(CONTAINS("X1", [Model Type]@row), INDEX({Other Sheet Range}, MATCH([Email 2]@row, {Other Sheet Email Range}, 0)), "")

IF(CONTAINS(INDEX({model}, MATCH([Email]@row, {assignee}, 0)), "X1"), INDEX({serial number}, MATCH([Email]@row, {assignee}, 0)), "")

My goal is to retrieve the serial # of a model column that contains "X1" in the name, using email addresses as the matching criteria. we have an asset inventory but some people have a desktop and laptop and i only need the laptops that have X1 in them and not Macbooks.

Any suggestions?

KPH

It sounds like you want to retrieve (INDEX) the serial number based on two criteria: The model column contains XI and the email address matches the email on the current sheet. In which case INDEX COLLECT will probably work. It would look something like:

=INDEX(COLLECT({serial number}, {Other Sheet Email Range}, [Email 2]@row, {model}, CONTAINS("XI", @cell)), 1)

I would wrap that in an IFERROR to return nothing if there isn't a match

=IFERROR(INDEX(COLLECT({serial number}, {Other Sheet Email Range}, [Email 2]@row, {model}, CONTAINS("XI", @cell)), 1), "")

This is returning the value in the column you have cross sheet referenced as {serial number} when the value in the column you have have cross sheet referenced as {Other Sheet Email Range} matches the value in Email 2 on the current row and the value in the the column you have cross sheet referenced as {model} contains "XI".

Hope this helps.

Trish Dillon

Thank you @KPH

- how would i add and OR statement to look up one or the other model? would it be like this?

=IFERROR(INDEX(COLLECT({serial number}, {assignee}, [Primary Column]@row, {model}, CONTAINS("Think", @cell, OR("Opti", @cell)), 1), ""))

https://community.smartsheet.com/discussion/comment/455027#Comment_455027

KPH

Almost. Try re-writing the formula like this:

=IFERROR(INDEX(COLLECT({serial number}, {assignee}, [Primary Column]@row, {model}, OR(CONTAINS("XI", @cell), CONTAINS("OPTI", @cell))), 1), "")