Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hlookup, but to next highest value
Hi All,
I'm using vba to enter the following code into some cells programatically: Range(strHlookupCell).Value = "=HLOOKUP(A3,TCtl_DateRange,1,TRUE)". where strHlookupCell = Sheet "PData" Range N3; and TCtl_DataRange = Sheets "Control" Range B5:BA5 TCtl_DataRange is a listing of dates incrementing by 1 week. What i need to be able to do, is look at cell A3 which is a date, and lookup the TCtl_DataRange and find the next highest match. Not all values in A3 will match perfectly to a value in TClt_DataRange. Is there a way to lookup the value in A3 and return either an exact match, or return the next highest value? The code im using returns the closest value, not the next highest value. Thanks. Tones. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hlookup, but to next highest value
Tony To find a match of A3 or next highest value: =INDEX(TctL_dateRange,MATCH(A3,TctL_dateRange)+NOT (INDEX(TctL_dateRange,MATCH(F1,TctL_dateRange))=A3 )) Don Pistulka "Tony Zappal" wrote in message ... Hi All, I'm using vba to enter the following code into some cells programatically: Range(strHlookupCell).Value = "=HLOOKUP(A3,TCtl_DateRange,1,TRUE)". where strHlookupCell = Sheet "PData" Range N3; and TCtl_DataRange = Sheets "Control" Range B5:BA5 TCtl_DataRange is a listing of dates incrementing by 1 week. What i need to be able to do, is look at cell A3 which is a date, and lookup the TCtl_DataRange and find the next highest match. Not all values in A3 will match perfectly to a value in TClt_DataRange. Is there a way to lookup the value in A3 and return either an exact match, or return the next highest value? The code im using returns the closest value, not the next highest value. Thanks. Tones. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hlookup, but to next highest value
Thank you heaps Don.
It works great. I had no idea about the index function. Cheers. Tones. "Don" wrote: Tony To find a match of A3 or next highest value: =INDEX(TctL_dateRange,MATCH(A3,TctL_dateRange)+NOT (INDEX(TctL_dateRange,MATCH(F1,TctL_dateRange))=A3 )) Don Pistulka "Tony Zappal" wrote in message ... Hi All, I'm using vba to enter the following code into some cells programatically: Range(strHlookupCell).Value = "=HLOOKUP(A3,TCtl_DateRange,1,TRUE)". where strHlookupCell = Sheet "PData" Range N3; and TCtl_DataRange = Sheets "Control" Range B5:BA5 TCtl_DataRange is a listing of dates incrementing by 1 week. What i need to be able to do, is look at cell A3 which is a date, and lookup the TCtl_DataRange and find the next highest match. Not all values in A3 will match perfectly to a value in TClt_DataRange. Is there a way to lookup the value in A3 and return either an exact match, or return the next highest value? The code im using returns the closest value, not the next highest value. Thanks. Tones. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hlookup, but to next highest value
Glad I could help
Don "Tony Zappal" wrote in message ... Thank you heaps Don. It works great. I had no idea about the index function. Cheers. Tones. "Don" wrote: Tony To find a match of A3 or next highest value: =INDEX(TctL_dateRange,MATCH(A3,TctL_dateRange)+NOT (INDEX(TctL_dateRange,MATCH(F1,TctL_dateRange))=A3 )) Don Pistulka "Tony Zappal" wrote in message ... Hi All, I'm using vba to enter the following code into some cells programatically: Range(strHlookupCell).Value = "=HLOOKUP(A3,TCtl_DateRange,1,TRUE)". where strHlookupCell = Sheet "PData" Range N3; and TCtl_DataRange = Sheets "Control" Range B5:BA5 TCtl_DataRange is a listing of dates incrementing by 1 week. What i need to be able to do, is look at cell A3 which is a date, and lookup the TCtl_DataRange and find the next highest match. Not all values in A3 will match perfectly to a value in TClt_DataRange. Is there a way to lookup the value in A3 and return either an exact match, or return the next highest value? The code im using returns the closest value, not the next highest value. Thanks. Tones. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hlookup, but to next highest value
My Bad
I meant to change the F1 to A3, like this: =INDEX(TctL_dateRange,MATCH(A3,TctL_dateRange)+NOT (INDEX(TctL_dateRange,MATCH(A3,TctL_dateRange))=A3 )) Don Pistulka "Don" wrote in message ... Tony To find a match of A3 or next highest value: =INDEX(TctL_dateRange,MATCH(A3,TctL_dateRange)+NOT (INDEX(TctL_dateRange,MATCH(F1,TctL_dateRange))=A3 )) Don Pistulka "Tony Zappal" wrote in message ... Hi All, I'm using vba to enter the following code into some cells programatically: Range(strHlookupCell).Value = "=HLOOKUP(A3,TCtl_DateRange,1,TRUE)". where strHlookupCell = Sheet "PData" Range N3; and TCtl_DataRange = Sheets "Control" Range B5:BA5 TCtl_DataRange is a listing of dates incrementing by 1 week. What i need to be able to do, is look at cell A3 which is a date, and lookup the TCtl_DataRange and find the next highest match. Not all values in A3 will match perfectly to a value in TClt_DataRange. Is there a way to lookup the value in A3 and return either an exact match, or return the next highest value? The code im using returns the closest value, not the next highest value. Thanks. Tones. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
highest second highest and third highest | Excel Discussion (Misc queries) | |||
Highest Value | Excel Discussion (Misc queries) | |||
Display the Highest, Second Highest, Third Highest and so on... | Excel Discussion (Misc queries) | |||
2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
second highest value | Excel Discussion (Misc queries) |