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.

--
Biff
Microsoft Excel MVP


"Raj" wrote in message
...
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

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

"Raj" wrote in message
...
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

On Aug 22, 4:54*am, "Ashish Mathur" wrote:
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

"Raj" wrote in message

...

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

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