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 GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

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
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!