How to look up a value?

[Kipi]

I have two column like this:
A B
1 23
2 21
4 20
1 19
4 18
....etc (The numbers in column A are random and do repeat)
What I want to do is to look up a specific value in column A, like 4, and
return the corresponding value of column B with the largest row number, in
this case it's 18 not 20
Any suggestions? =)

 

[Mike H]

Hi,

Try this. The 4 in the middle of the formula is the lookup value

=LOOKUP(2,1/(A1:A20=4),B1:B20)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Kipi]

I have two column like this:
A B
1 23
2 21
4 20
1 19
4 18
...etc (The numbers in column A are random and do repeat)
What I want to do is to look up a specific value in column A, like 4, and
return the corresponding value of column B with the largest row number, in
this case it's 18 not 20
Any suggestions? =)

Btw the values in column B are also random and repeats.

 

[Kipi]

Thanks for the advice. However, the values in column A and B are random, 4
being in middle is just a coincidence so I don't think it works.
I did a little bit search and found a MATCH function that will return the
NUMBER of match data entry, like if I MATCH with coefficient 4, it returns 2,
meaning 2 entries of "4" in column A.....dunno if this could help

 

[Mike H]

I suggest you try it. It will find the last instance of the lookup value in
column A and return the corresponding value from column B.

It doesn't have to be a 4 for the lookup value, I used that because you did
in your original question.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Kipi]

Just an amendment. I tried it later and it DOES work! Thank you Mike!
But on top of that can I also return the row number in which the lookuped
value is in?
Thanks again.

 

[Mike H]

Fir the row number use

=LOOKUP(2,1/(A1:A20=4),ROW(A1:A20))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Kipi]

It works great! Thanks for your help!

 

[Mike H]

Your welcome
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Ashish Mathur]

Hi,

You may also try this formula. D6:E10 is your range of data. D13 contains
4

=INDEX($D$6:$E$10,MAX(INDEX(($D$6:$D$10=D13)*(ROW(E6:E10)-ROW($E$5)),,1)),2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com