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

BULLISHBEAR

New Member
Joined
Sep 7, 2010
Messages
2
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
 

Some videos you may like

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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Sep 7, 2010
Messages
2
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,372
Messages
5,444,070
Members
405,265
Latest member
Iram

This Week's Hot Topics

Top