Excel Web Query

[David Lipetz]

I'm attempting to use Web Query to import a Web-based table into Excel in
order to perform lookups on the imported data.

The reference point is customer number - common to both my Excel sheets and
the Web-based table.

The problem I have is that the Web-based table is not sorted by customer
number, hence I can not use the VLOOKUP function to transfer relevant
information from the Web table to the Excel sheet.

Yes, I know I can sort the imported table, but the data is dynamic and I'd
like the sheet to be up to date with each refresh. That is not possible as
the data changes back to the unsorted state upon each refresh.

Is there a way to tell the Web Query to sort the data on customer number
upon import? Or is there another way of going about this?

By the way, Web Query is incredibly cool. I just stumbled upon it today.

Thanks,
David

 

[mudraker]

David


Have a look at the INDEX & MATCH function
Data does not need to be sorted

Syntax
INDEX(reference,row_num,column_num,area_num)
MATCH(lookup_value,lookup_array,match_type)

Example
=index(c1:f12,match(a1,f1:f12,0),2

 

[Ardus Petus]

=VLOOKUP(what, where, column, FALSE)

If you enter FALSE as 4th argument, VLOOKUP does not require data to be
sorted

HTH

 

[David Lipetz]

Wow. I did not realize that. I've always used the FALSE parameter to make
sure that only exact hits were reported, but did not know that it also
eliminated the need for sequential data.

HUGE thank you!!

David