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

#### BULLISHBEAR

##### New Member
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

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### Andrew Poulsom

##### MrExcel MVP
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

##### New Member
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!!