index function - ascending order

[willemeulen]

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

 

[JLatham]

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.