How to find the reference of a value in a table, when i know it is present in the table?

[BULLISHBEAR]

Hi,

I was trying to find the cell reference of a particular value, if found in a table. For eg. If i have a table having prices for difference currencies and for different quantities. Assuming the currencies name in Cells: D2:D20 and Quantities in Cells E1:M1. Corresponding data is in table E2:M20. If I have some price mentioned in Column A, how do i get the currencies and quantity respectively in Columns B and C respectively?
I hope that my questions is easy to understand!

Tx

 

[Andrew Poulsom]

Welcome to the Board.

If the prices are unique:

=INDEX(D$2:D$20,SUMPRODUCT(--(E$2:M$20=A1)*(ROW(E$2:M$20)-ROW(A$1))))

=INDEX(E$1:M$1,SUMPRODUCT(--(E$2:M$20=A1)*(COLUMN(E$2:M$20)-COLUMN(D$1))))

 

[BULLISHBEAR]

Tx a lot Andrew for the prompt response! It worked perfectly as prices were unique in the table. I'll try to understand the logic and post my comments appropriately! Tx again!!