How to look up a value?
in [Functions]
|
From: Kipi on 13 May 2010 06:02 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 >
From: Mike H on 13 May 2010 06:12 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 > > >
From: Kipi on 13 May 2010 06:17 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. >
From: Mike H on 13 May 2010 06:19 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. > > >
From: Ashish Mathur on 23 May 2010 19:46 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" <Kipi(a)discussions.microsoft.com> wrote in message news:EE2142B8-73D6-4D08-BAAC-198649EE6AB3(a)microsoft.com... > 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? =)
First
|
Prev
|
Pages: 1 2 Prev: sumif across multiple sheets in excel 2007 based on a conditio Next: convert matrices to table |