Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index function - ascending order
I'm using the index function to lookup data from the web (web query). Curently I'm using 4 web query's to update information (set to refresh on opening the document). Luckily I see by hiding certain cells (heading cells) it does not affect the data when refreshed. My challange is that the data is not in acsending order, I know with vlookup functions this would be a problem. How does this work with index functions. If not mistaken I could get all in ascending order but than I will need to split the web query's, instead of the 4 I will end up with about 40 web query's! Currently I usde index to lookup values in column A, B and C, when matching it returns the price in the column 8 Thanks, W:Bgr -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105186 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index function - ascending order
Sounds like you're worried about VLOOKUP() not working with an unsorted list.
You can set up the VLOOKUP() to look through an unsorted list and return related information for the first match it does find. Here's an example =VLOOKUP(X1,A1:C99,2, FALSE) the FALSE tells it the list may not be a sorted list in column A. For MATCH and INDEX, index doesn't much care, it just takes the value to return an entry in that position. Your concern may be MATCH() which is often used to find the position of an item in a list to be used as an INDEX value. You can tell MATCH() to find an exact match, and then it doesn't care whether your list is sorted or not. Example: =MATCH(9,A1:A99,0) That would return the position of the (first) value 9 in the list in A1:A99 regardless of what numbers precede it, and whether or not they are larger or smaller than 9. So if A1:A4 contained 1 44 6 9 The result would be 4 (4th item in the list). Hope this helps. "willemeulen" wrote: I'm using the index function to lookup data from the web (web query). Curently I'm using 4 web query's to update information (set to refresh on opening the document). Luckily I see by hiding certain cells (heading cells) it does not affect the data when refreshed. My challange is that the data is not in acsending order, I know with vlookup functions this would be a problem. How does this work with index functions. If not mistaken I could get all in ascending order but than I will need to split the web query's, instead of the 4 I will end up with about 40 web query's! Currently I usde index to lookup values in column A, B and C, when matching it returns the price in the column 8 Thanks, W:Bgr -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105186 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Function Not in Ascending Order | Excel Worksheet Functions | |||
How do I # my rows in ascending order. | Excel Worksheet Functions | |||
Digits in ascending order ? | Excel Worksheet Functions | |||
How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions | |||
sorting number in ascending order | Excel Discussion (Misc queries) |