Returning row and column headers of a value

[Raj]

Hi,

I have a sheet with data as follows:

Version1 Version2 Version3
Product1 150 175 250
Product2 225 180 395
Product3 145 170 360

The table has all distinct values (no duplicates/repetitions)

In a cell away from the table, the user inputs one of the table values
eg. 170, in the adjacent columns the row header viz. Product3 and
column header viz. Version2 should be displayed.

Can this be done using Excel native functions? Or UDFs?

Thanks in advance for the help.

Regards,
Raj

 

[T. Valko]

Try these array formulas**.

Assume your data is in the range A14

G2 = 170

For the column header:

=INDEX(B11,MAX((B24=G2)*COLUMN(B24))-COLUMN(B2)+1)

For the row header:

=INDEX(A2:A4,MAX((B24=G2)*ROW(B24))-ROW(B2)+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Ashish Mathur]

Hi,

This is to know the version

=CHOOSE(SUMPRODUCT((G6:J9=G12)*((COLUMN(G6:J9)-COLUMN(G6)))),H6,I6,J6)

This is to know the product

=CHOOSE(SUMPRODUCT((G6:J9=G12)*((ROW(G6:J9)-ROW(G6)))),G7,G8,G9)

The data is in range G6:J9. G12 holds 170


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Rajendra]

Hi,

This is to know the version

=CHOOSE(SUMPRODUCT((G6:J9=G12)*((COLUMN(G6:J9)-COLUMN(G6)))),H6,I6,J6)

This is to know the product

=CHOOSE(SUMPRODUCT((G6:J9=G12)*((ROW(G6:J9)-ROW(G6)))),G7,G8,G9)

The data is in range G6:J9.  G12 holds 170

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

Hi Ashish,

Thanks a lot for that solution. I was interested in knowing how it all
works so that I could use that learning on future occasions in other
contexts. Please help.

Thanks in Advance.

Regards,
Rajendra