0

I am working with different excel files which contain a lot of data from different people. I would like to do a lookup (I know how to use the LOOKUP function) but based on multiple criteria. Let me explain with an exemple, I have a file that look like this (I use / as a column delimiter) :

Name of the data provider / Data / Data code
John / 55.5 / SSRF5
Amanda / 55.6 / SSRF5
Alicia / 54 / SSRF5
John / 22.5 / ABRD4
Amanda / blank / ABRD4
Alicia / 22.6 / ABRD4
John / blank / FFGR2
Amanda / blank / FFGR2
Alicia / 45.6 / FFGR2

Note that I used "blank" to denote the fact that I have entries that have no data. I have also another file where I have a list of the Data codes, to which I would like to associate a data to obtain a file of the form

Data code / Data
SSRF5 / ...
ABRD4 / ...
FFGR2 / ...

As you can see, I may have multiple data for each data code and/or lack data from certain providers, and I would like to use the Name of the data provider to choose which one shall be used. For example, in this example, I would use the data from Amanda in priority, if not available I'd preferably use John and if I don't have a choice, I'll use Alicia.

Note that I have a finite set of data providers and that I am able to define a strict order between them. Do you have any idea of how I could do that? As my file is very big, I just cannot do it by hand.

Thanks in advance.

PS: I use Microsoft Excel 2010 on Windows 7.
PPS: I have never used VBA so if your solution needs it, please do not make too much shorcuts. :-)

0

I would use the Power Query Add-In. It can import data from text files, spreadsheets, databases etc. It has a Merge function which is roughly similar to a lookup. You can probably chain multiple Queries together with multiple Merge operations to achieve your result.

http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872

You can deliver the final result into an Excel table.

  • Thanks, but this seems to complex for me. I'll continue looking. :-) – Math Jul 9 '14 at 13:45
  • Your requirement is complex. A Power Query solution is going to be simpler than any VLOOKUP or VBA solution. Good luck! – Mike Honey Jul 10 '14 at 2:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.