Getting the next higer value with Vlookup - True
in [Programming]
|
From: getu32 on 3 Feb 2010 05:35 In VLookup / Hlookup function the True parameter gives the closest match with the search value but less than the search value.... i want to get the next greatest values of the Search Value.... how to do it.... Example ..... A B C 5 10 15 6 11 16 9 14 19 If i use =Vlookup(7,[Array],2,0)/ =Vlookup(7,Array],2,True) .... the answer is get is 11 BUT how to get it as 14!!!??? Please reply me at : getu32(a)gmail.com
From: Roger Govier on 3 Feb 2010 05:57 Hi One way =INDEX(B2:B4,MATCH(7,A2:A4,1)+1) -- Regards Roger Govier "getu32" <getu32(a)gmail.com> wrote in message news:a1d39786-a5ee-43d9-877b-0cc75c4737c1(a)o28g2000yqh.googlegroups.com... > In VLookup / Hlookup function the True parameter gives the closest > match with the search value but less than the search value.... > > i want to get the next greatest values of the Search Value.... how to > do it.... > > Example ..... > > A B C > 5 10 15 > 6 11 16 > 9 14 19 > > If i use =Vlookup(7,[Array],2,0)/ > =Vlookup(7,Array],2,True) .... > the answer is get is 11 > > BUT how to get it as 14!!!??? > > Please reply me at : getu32(a)gmail.com > > __________ Information from ESET Smart Security, version of virus > signature database 4830 (20100203) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > __________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________ The message was checked by ESET Smart Security. http://www.eset.com
From: Jacob Skaria on 3 Feb 2010 06:27 If the values are sorted and if you are looking a match within the specified values try the below... In cell E1 enter the query number =INDEX(B:B,IF(ISNA(MATCH(E1,A:A,0)),MATCH(E1,A:A,1)+1,MATCH(E1,A:A,0))) -- Jacob "getu32" wrote: > In VLookup / Hlookup function the True parameter gives the closest > match with the search value but less than the search value.... > > i want to get the next greatest values of the Search Value.... how to > do it.... > > Example ..... > > A B C > 5 10 15 > 6 11 16 > 9 14 19 > > If i use =Vlookup(7,[Array],2,0)/ > =Vlookup(7,Array],2,True) .... > the answer is get is 11 > > BUT how to get it as 14!!!??? > > Please reply me at : getu32(a)gmail.com > . >
From: Mike H on 3 Feb 2010 06:30 Hi, Try this array formula, the lookup value is in C1 and the lookup array is in A1 - B10 =INDEX(B1:B10,MATCH(TRUE,A1:A10>=C1,0)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "getu32" wrote: > In VLookup / Hlookup function the True parameter gives the closest > match with the search value but less than the search value.... > > i want to get the next greatest values of the Search Value.... how to > do it.... > > Example ..... > > A B C > 5 10 15 > 6 11 16 > 9 14 19 > > If i use =Vlookup(7,[Array],2,0)/ > =Vlookup(7,Array],2,True) .... > the answer is get is 11 > > BUT how to get it as 14!!!??? > > Please reply me at : getu32(a)gmail.com > . >
From: Jacob Skaria on 3 Feb 2010 06:39
and what if an exact match? -- Jacob "Roger Govier" wrote: > Hi > > One way > =INDEX(B2:B4,MATCH(7,A2:A4,1)+1) > > -- > Regards > Roger Govier > > "getu32" <getu32(a)gmail.com> wrote in message > news:a1d39786-a5ee-43d9-877b-0cc75c4737c1(a)o28g2000yqh.googlegroups.com... > > In VLookup / Hlookup function the True parameter gives the closest > > match with the search value but less than the search value.... > > > > i want to get the next greatest values of the Search Value.... how to > > do it.... > > > > Example ..... > > > > A B C > > 5 10 15 > > 6 11 16 > > 9 14 19 > > > > If i use =Vlookup(7,[Array],2,0)/ > > =Vlookup(7,Array],2,True) .... > > the answer is get is 11 > > > > BUT how to get it as 14!!!??? > > > > Please reply me at : getu32(a)gmail.com > > > > __________ Information from ESET Smart Security, version of virus > > signature database 4830 (20100203) __________ > > > > The message was checked by ESET Smart Security. > > > > http://www.eset.com > > > > > > > > __________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > > . > |