If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to look up a value?
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? =) |
#2
|
|||
|
|||
How to look up a value?
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" wrote: 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? =) |
#3
|
|||
|
|||
How to look up a value?
"Kipi" wrote: 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. |
#4
|
|||
|
|||
How to look up a value?
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" wrote: 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. |
#5
|
|||
|
|||
How to look up a value?
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" wrote: 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" wrote: 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. |
#6
|
|||
|
|||
How to look up a value?
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. "Kipi" wrote: 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 |
#7
|
|||
|
|||
How to look up a value?
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" wrote: 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. "Kipi" wrote: 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 |
#8
|
|||
|
|||
How to look up a value?
It works great! Thanks for your help!
"Mike H" wrote: 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. |
#9
|
|||
|
|||
How to look up a value?
Your welcome
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kipi" wrote: It works great! Thanks for your help! "Mike H" wrote: 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. |
#10
|
|||
|
|||
How to look up a value?
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 "Kipi" wrote in message ... 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? =) |
Thread Tools | |
Display Modes | |
|
|