SOLVED

RETURN ANOTHER CELL OFF VALUE FOUND

Occasional Contributor

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?

4 Replies
best response confirmed by Paul_Bragason (Occasional Contributor)
Solution

@Paul_Bragason 

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.

Worked like a charm! Thank you!
How would it work it was on another sheet?

@Paul_Bragason 

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