For example, I have two columns as follows:
A B C D
11 50 10 35
23 20
35 10
47 40
59 30

I need the Excel to look up the row first where the value of the column B
equals to cell C. And then, at the same row, pick up the value in column A
and input it into cell D.

Thank you very much.

=INDEX(A1:A5,MATCH(C1,B1:B5))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Yuanhang" wrote in message ...
| For example, I have two columns as follows:
| A B C D
| 11 50 10 35
| 23 20
| 35 10
| 47 40
| 59 30
|
| I need the Excel to look up the row first where the value of the column B
| equals to cell C. And then, at the same row, pick up the value in column A
| and input it into cell D.
|
| Thank you very much.
|

Try this in d1

=OFFSET(B1,MATCH(C1,B1:B5,0)-1,-1,1,1)

Mike

"Yuanhang" wrote:

For example, I have two columns as follows:
A B C D
11 50 10 35
23 20
35 10
47 40
59 30

I need the Excel to look up the row first where the value of the column B
equals to cell C. And then, at the same row, pick up the value in column A
and input it into cell D.

Thank you very much.

For example, I have two columns as follows:
A B C D
11 50 10 35
23 20
35 10
47 40
59 30

I need the Excel to look up the row first where the value of the column B
equals to cell C. And then, at the same row, pick up the value in column A
and input it into cell D.

Oh, why not... let's add to the mix of answers...

=INDIRECT("A"&MATCH(C1,$B$1:$B$100,0))

Rick