SOLVED

# RETURN ANOTHER CELL OFF VALUE FOUND

Occasional Contributor

# 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?

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

# Re: RETURN ANOTHER CELL OFF VALUE FOUND

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.

# Re: RETURN ANOTHER CELL OFF VALUE FOUND

Worked like a charm! Thank you!

# Re: RETURN ANOTHER CELL OFF VALUE FOUND

How would it work it was on another sheet?

# Re: RETURN ANOTHER CELL OFF VALUE FOUND

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