Find a value and return value of the row and column header

[JG2011]

Hi All,

I have a formulae problem, hope to receive some help!

Example/Scenario:
A Table
Row 1 - list of name, eg. David, James, Mary, etc
Column 1 - list of months, eg. Jan, Feb, Mar, etc
Middle of table (content of table) are eg. red, orange, green, blue, pink, etc

Problem:
I would like to search for red from the table content.
And, the result to return 'name' in row 1 and 'month' in column 1.

eg. red is sitting at intercept of James and Mar.
So, to search for red, and return 'James' and 'Mar'.

May any expert help me out please? Much appreication in advance.

Thank you!

[TMS]

@JG2011: welcome to the forum.

Please take a few moments to read the forum rules and amend your thread title to something more specific and descriptive of your problem.

A sample workbook is always helpful in developing a relevant and practical solution.

Regards

[JG2011]

Hi, may i ask, how to change the title and also attach a sample workbook pls? Thanks!

[JG2011]

Hi All,

I have a formulae problem, hope to receive some help!

Example/Scenario:
A Table
Row 1 - list of name, eg. David, James, Mary, etc
Column 1 - list of months, eg. Jan, Feb, Mar, etc
Middle of table (content of table) are eg. red, orange, green, blue, pink, etc

Problem:
I would like to search for red from the table content.
And, the result to return 'name' in row 1 and 'month' in column 1.

eg. red is sitting at intercept of James and Mar.
So, to search for red, and return 'James' and 'Mar'.

May any expert help me out please? Much appreication in advance.

Thank you!

[TMS]

For both, click on Edit and then the Go Advanced button. You should be able to edit the title there and upload a sample (seems you've achieved the second task)

Regards

[JG2011]

For both, click on Edit and then the Go Advanced button. You should be able to edit the title there and upload a sample (seems you've achieved the second task)

Regards

Thanks! I did it!

[TMS]

Have a look at the attached sample workbooks.

I can't claim any credit other than applying them to your data.

The expertise has come from Mr Excel and Excel Is Fun (Trick No 7)

I have to say, the formula approach is not the simplest but it works and avoids VBA. It's be interesting to see how easy or difficult you find it to use in your live workbook.


There is also a VBA solution from the same source which I have adapted. Much easier to use but, as the name implies, it requires VBA


Regards

[JG2011]

First, really thanks for your effort to look into this and reply so quickly.

May I ask, is there any simplier solution please? They are so complex for me to understand and follow.

Thanks.

[JG2011]

how to apply VBA pls? Thanks.

[TMS]

On Excel, press Alt-F11 to open the VBA Editor.

On the menu, click on Insert | Module

Copy and paste the code shown below:

Please Login or Register  to view this content.

You then use the User Defined Function (UDF) like a normal function, thus:

=RL(G9,$B$2:$F$6)

Where G9 has the search item and $B$2:$F$6 is the table to be searched. Note that the table range needs to be absolute (with $ signs) if you plan to copy it.

Regards

[JG2011]

hi, thanks. let me try. hope it works. tx.

[JG2011]

Great. This is solved. I used the VBA. Though I cannot understand how it works, but it just work. Thanks!

[TMS]

You're welcome.