[Excel] Complicated Data Extraction

[Lion_Engineer]

Salutations!

Recently I've been given the task of trying to take a list of items from one sheet, and consolidating them into a better format.
Unfortunately for me, this list of items needs to be cross-referenced with another sheet to check if the item must be included or not, a system which is not strictly one-to-one (Sometimes an item is listed twice on the reference sheet, and must be checked under both entries)

So far it's been easy getting the data from the reference sheet, and I can very easily extract one column of data, then find the corresponding row entry that's been checked off, however I'm running into trouble extracting that information across multiple columns.

In a programming sense, I want to do a lookup with multiple outputs for each column entry, then repeat that process for however many duplicate entries there are while appending to the original lookup results, but it looks like there aren't many Excel functions to deal with this?

I've given the following a try:
[ul]
[li]VLOOKUP[/li]
[li]XLOOKUP[/li]
[li]FILTER[/li]
[li]FILTER w/ conditional array attached[/li]
[li]AGGREGATE[/li]
[/ul]

If anyone else has had to do a similarly complicated referencing using Excel, I'd love to hear about it.

 

[IDS]

Two options that come to mind are:

1) Use MATCH and INDEX with helper columns so you can do the lookups in stages.
2) Do it in VBA, probably using scripting dictionaries.

For more specific advice, some examples of the sort of data giving problems would be helpful.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

As with almost any and every complicated situation, the devil is in the details. And until that is revealed, the best anyone can offer has been offered.

Oh and I could offer another possible alternative method of some sort of SQL solution.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[EireChch]

use the website Fiver. pay someone in India to do it for 5 dollars.

No joke, I use it all the time for data extraction.

Its great as you learn many tricks from their work.