Formula help with Index Match and Contains

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?

Find more posts tagged with

Formulas

Accepted answers

All comments

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), "")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.