Returning row and column headers of a value

  • Thread starter Thread starter Raj
  • Start date Start date
R

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
 
Try these array formulas**.

Assume your data is in the range A1:D4

G2 = 170

For the column header:

=INDEX(B1:D1,MAX((B2:D4=G2)*COLUMN(B2:D4))-COLUMN(B2)+1)

For the row header:

=INDEX(A2:A4,MAX((B2:D4=G2)*ROW(B2:D4))-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.
 
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
 
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
Click to expand...

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
 
Back
Top