Why does the INDEX/MATCH formula return the same value for same value cells on different rows

I am using an INDEX/COLLECT Formula: =INDEX(COLLECT({IP}, {Site}, SIte@row, {Lane Number and Direction}, [Lane Number/Direction]@row), 1)

This works great. Except for when the Lane Number/Direction is the same value in the rows.

I have a several rows of Sites with the same value (BK-1-1E). The Lane Number/Direction is the same value for some, but each row has a different IP.

Smartsheet is repeating the same IP on all the rows. See my IP Address column below. It should be picking up a different IP Address on each line, but it repeats the result of the first calculation.

So on Row Site BK-1-1E(1) is correct to have IP Address 0.031, but Row 3 and the others below show the same IP Address when it should be different.

How do I fix this? Thanks :-)

Find more posts tagged with

Formulas

Accepted answers

All comments

AravindGP

Hi @SJTA

If you've the same combination of Site and Lane Number/Direction, it is going to pick up the first value it finds with that combination. Unless there's another differentiator, the value will remain the same as your formula is trying to find the first match of IP address for Site and Lane Number/Direction.

JamesB

@SJTA

Formulas individually always execute from the top of the sheet. In your formula you do not have distinct site names and then in Lane/Number Direction you have many blank rows. So the first instance your formula finds a match is where it will return a result. In this case 0.031 is the same result because the site name BK-1-1E (1) and blank are valid in your formula, the result is returning the same answer. You need to have something that will make each row independent to reference against. Example in your description row instead of just DPS you could have DPS1 and DPS2 to give you distinct values to formulate against.

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.