Forum Discussion
Solved
RETURN ANOTHER CELL OFF VALUE FOUND
Hi There, I am needing some sort of formula to tell me where my parts are. In the example attached B10 should read "RACK 2". I'm sure it's simple enough, right?
In B10:
=INDEX($B$1:$E$1,SUMPRODUCT(($B$2:$E$5<>"")*($A$2:$A$5=A10)*(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)))
If you don't have Microsoft 365 or Office 2021, you may have to confirm the formula by pressing Command+Return.
In B10:
=INDEX($B$1:$E$1,SUMPRODUCT(($B$2:$E$5<>"")*($A$2:$A$5=A10)*(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)))
If you don't have Microsoft 365 or Office 2021, you may have to confirm the formula by pressing Command+Return.
- How would it work it was on another sheet?
Let's say the table was on a sheet named Data Sheet.
=INDEX('Data Sheet'!$B$1:$E$1,SUMPRODUCT(('Data Sheet'!$B$2:$E$5<>"")*('Data Sheet'!$A$2:$A$5=A10)*(COLUMN('Data Sheet'!$B$1:$E$1)-COLUMN('Data Sheet'!$B$1)+1)))
- Worked like a charm! Thank you!
