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. :-)