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 (BK11E). 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 BK11E(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 :)
Answers

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.
Thanks,
Aravind
Reach out for any help on licenses, configuration, or training

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 BK11E (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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!