Return values with 2 criteria for matching (multiple lookup)

Question

Happy New 2015 Year and all the best! I would like to thank you on behalf of all requestors Big thank you for all your valuable help!

And would like to once again ask for help. I try to use your advices, but didn't succeed. So I have 2 xls files: first with columns - project id and period second -project id, period and costs

I would like to return the values of the costs (from the second file) in the first. And in the second file I don't have all the projects id with all periods. I try with index match but it gives me an error.

first file

Project ID NEW  Month   TEST
RS.700006.001.01    Start   
RS.700006.001.01    2014-01 01.01.2014
RS.700006.001.01    2014-02 01.02.2014
RS.700006.001.01    2014-03 01.03.2014
RS.700006.001.01    2014-04 01.04.2014
RS.700006.001.01    2014-05 01.05.2014
RS.700006.001.01    2014-06 01.06.2014
RS.700006.001.01    2014-07 01.07.2014
RS.700006.001.01    2014-08 01.08.2014
RS.700006.001.01    2014-09 01.09.2014
RS.700006.001.01    2014-10 01.10.2014
RS.700006.001.01    2014-11 01.11.2014
RS.700006.001.01    2014-12 01.12.2014
RS.700006.001.01    Rest    Rest
RS.700032.001.01    Start   Start
RS.700032.001.01    2014-01 01.01.2014
RS.700032.001.01    2014-02 01.02.2014
RS.700032.001.01    2014-03 01.03.2014
RS.700032.001.01    2014-04 01.04.2014
RS.700032.001.01    2014-05 01.05.2014
RS.700032.001.01    2014-06 01.06.2014
RS.700032.001.01    2014-07 01.07.2014
RS.700032.001.01    2014-08 01.08.2014
RS.700032.001.01    2014-09 01.09.2014
RS.700032.001.01    2014-10 01.10.2014
RS.700032.001.01    2014-11 01.11.2014
RS.700032.001.01    2014-12 01.12.2014
RS.700032.001.01    Rest    Rest
RS.700036.001.01    Start   Start
RS.700036.001.01    2014-01 01.01.2014
RS.700036.001.01    2014-02 01.02.2014
RS.700036.001.01    2014-03 01.03.2014
RS.700036.001.01    2014-04 01.04.2014
RS.700036.001.01    2014-05 01.05.2014
RS.700036.001.01    2014-06 01.06.2014
RS.700036.001.01    2014-07 01.07.2014
RS.700036.001.01    2014-08 01.08.2014
RS.700036.001.01    2014-09 01.09.2014
RS.700036.001.01    2014-10 01.10.2014
RS.700036.001.01    2014-11 01.11.2014
RS.700036.001.01    2014-12 01.12.2014
RS.700036.001.01    Rest    Rest

second file

project Year    per Month   costs
RS.700006.001.01    2014    01  1.01.2014    4006148,78
RS.700006.001.01    2014    02  1.02.2014    3664679,47
RS.700006.001.01    2014    03  1.03.2014    5268806,92
RS.700006.001.01    2014    04  1.04.2014    4963940,91
RS.700006.001.01    2014    05  1.05.2014    4636197,32
RS.700006.001.01    2014    06  1.06.2014 3748324,55
RS.700006.001.01    2014    07  1.07.2014    5684306,96
RS.700006.001.01    2014    08  1.08.2014    4389276,85
RS.700006.001.01    2014    09  1.09.2014    6211733,48
RS.700006.001.01    2014    10  1.10.2014    6741091,31
RS.700006.001.01    2014    11  1.11.2014    934213,12
RS.700006.001.01    2014    12  1.12.2014    0
RS.700032.001.01    2014    03  1.03.2014    77979,36
RS.700032.001.01    2014    04  1.04.2014    50468,95
RS.700032.001.01    2014    05  1.05.2014    115489,6
RS.700032.001.01    2014    06  1.06.2014    121264,08
RS.700032.001.01    2014    07  1.07.2014    103940,64
RS.700032.001.01    2014    08  1.08.2014    1000,2
RS.700032.001.01    2014    09  1.09.2014    101313,04
RS.700032.001.01    2014    10  1.10.2014    65806,12
RS.700032.001.01    2014    11  1.11.2014    32548,01
RS.700036.001.01    2014    06  1.06.2014    142283,04
RS.700036.001.01    2014    07  1.07.2014    190442,24
RS.700036.001.01    2014    08  1.08.2014    104171,6
RS.700036.001.01    2014    09  1.09.2014    115096,38
RS.700036.001.01    2014    10  1.10.2014    115096,37
RS.700036.001.01    2014    11  1.11.2014    111204,14
RS.700036.001.01    2014    12  1.12.2014   0

Thank you very much!

BR Gerrie

Did you find anything here useful? You did not post ant feedback, — sancho.s ReinstateMonicaCellio, Jan 16 '15 at 21:48

score 0 · Answer 1 · answered Jan 09 '15 at 08:43

It is probably easiest to create a single workbook with a sheet for each of the above in it. Then try using the VLOOKUP function to match the costs that you have. For this to work best it is sometimes best to make sure that the second list (with costs) is sorted in order of project id.

score 0 · Answer 2 · edited May 23 '17 at 11:50

This is another case of multiple lookup. Please see the summary of options here.

For instance, use something like.

=LOOKUP(2,1/(ws1!A$2:A$1000=A2)/(ws1!D$2:D$1000=C2),ws1!E$2:E$1000)

This goes in a new column in worksheet 2. Copy downwards as needed.

I am assuming the following:

Your second worksheet has a generic name ws1. Please substitute with a suitable reference.
You want to match column A - column A, column D - column C (worksheet 2 - worksheet 1); and output column E.
Your source rows are 2-1000.

Return values with 2 criteria for matching (multiple lookup)

2 Answers2