How to look up a value?
in [Functions]
|
From: Kipi on 13 May 2010 04:53 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? =)
From: Mike H on 13 May 2010 05:07 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? =)
From: Kipi on 13 May 2010 05:08 "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.
From: Kipi on 13 May 2010 05:57 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. > >
From: Mike H on 13 May 2010 06:01 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. > > > > >
|
Next
|
Last
Pages: 1 2 Prev: sumif across multiple sheets in excel 2007 based on a conditio Next: convert matrices to table |