How to look up a value?


K

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? =)
 
Ad

Advertisements

M

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

Kipi

Kipi said:
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.
 
K

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
 
M

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

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

Advertisements

M

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

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

Advertisements

A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top