Search for row and column title

[soldelta]

Hi

I need formula which will show column title and row title in table for specific data which i will ask for. Note that data which i will look for will be always unique.
forum.png

I tried =INDEX($C$35:$E$35,MATCH($A$36,C36:E36,0)) but its only working in one column or in one row, not in multiple area. Same with HLOOKUP and VLOOKUP.

Thank you in advance for help.

P

[benishiryo]

welcome to the forum, soldelta. do upload an excel sample so that we do not have to manually key in your data to do a testing.

input the desired results like you did so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

hopefully, i did a correct guess. otherwise, i have wasted time coming up with something that does not help you. if i really guessed it wrongly, please upload something according to my recommendations. try to use the solution i have given and we'll help to advise what went wrong.

when you say "data which i will look for will be always unique.", you mean "B" or "CCC" will only appear once in that particular column? try:
=INDEX($B$34:$B$36,MATCH(H33,INDEX($C$34:$E$36,,MATCH(H34,$C$33:$E$33,0)),0))

[soldelta]

Hi

Sorry for not following forum rules.
When i open your file, i need to put data in both "Search for" and "day". Then your formula in "Drawer" will show me name. What i need is just to put "Search for" and both day and drawer will show correct column and row title.

P

[benishiryo]

in my file, change H34 to:
=INDEX(C33:E33,SUMPRODUCT((C34:E36=H33)*COLUMN(C34:E36))-COLUMN(C34)+1)

H35 to:
=INDEX(B34:B36,SUMPRODUCT((C34:E36=H33)*ROW(C34:E36))-ROW(C34)+1)

[soldelta]

Works, thank you very much for help!