Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the next higer value with Vlookup - True
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 : |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the next higer value with Vlookup - True
Hi
One way =INDEX(B2:B4,MATCH(7,A2:A4,1)+1) -- Regards Roger Govier "getu32" wrote in message ... 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 : __________ 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the next higer value with Vlookup - True
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 : . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the next higer value with Vlookup - True
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 : . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the next higer value with Vlookup - True
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" wrote in message ... 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 : __________ 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting the next higer value with Vlookup - True
Mea culpe!!!
I posted in haste without thinking it through. You are quite right Jacob - and your posting is the answer I should have posted. -- Regards Roger Govier "Jacob Skaria" wrote in message ... 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" wrote in message ... 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 : __________ 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 . __________ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup to bring in not true data. | Excel Discussion (Misc queries) | |||
VLOOKUP Range_lookup TRUE | New Users to Excel | |||
vlookup with result TRUE not updating cell | Excel Worksheet Functions | |||
Vlookup to return the next true value | Excel Discussion (Misc queries) | |||
vlookup, true false issue | Excel Programming |