How to return to the value of a cell?

[Guest]

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.

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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.
|

 

[Guest]

Try this in d1

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

Mike

 

[Rick Rothstein \(MVP - VB\)]

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