How to Look up Based on Multiple Conditions and tables

[BrucevB]

Hi, I have a muti dimensional data set with two variables that I wish to merge into a single worksheet. If you look at the the attached example the data is in the format 'dhid-from-to-var1 or var2' depending upon the worksheet (Table1 or Table2). 'var1' and'var2' represent 2 different ways of measuring the same attribute, but were not collected everywhere in the case of 'var1' so I want to fill in the missing values (missing = -8) var1 values with the corresponding value from 'var2' in Table2. In the original data the variable 'dhid' does change, so 'from' and 'to' values will repeat as the 'dhid' changes.

For each 'dhid' the 'from' and 'to' values may differ for a few values between the two measurements, that data does not interest me. Where the 'dhid' 'from' and 'to' values match I want to copy the var2 value across from Table2 to Table1.

Hope I have explained this clearly and thanks for your time if you reply.

 

[Luke M]

Welcome to the forum Bruce!
In worksheet Table1, cell E2, put this formula:
=SUMIFS(Table2!D:D,Table2!A:A,A2,Table2!B:B,B2,Table2!C:C,C2)

Copy down as needed. Note that row 2 has a value of 0, as Table2 had a From/To combination of 0-0.95, and 0.95-1, but not a 0-1 combo (I wasn't sure how you wanted to handle that).

 

[BrucevB]

Thanks for the quick response Luke.

As the size of the file increases would you consider using another option than SUMIFS? I saw an example on this site that utilized a combination of Match and Index to do essentially the same thing, do you think one method is faster than another?

 

[Luke M]

Hi Bruce,

Good point. I'd suggest creating XL tables with your data, and then replacing the entire column references with structural references. That will limit the size of the arrays to just the rows with data, and not ~ 1 million blank rows.

In terms of SUMIFS vs. INDEX/MATCH, in this case, the calculation is about the same. The MATCH structure would still require you to build an array for each of the criterias, and then compare all those arrays together. You could argue that there's a slight tilt towards using INDEX as we really only need to grab one value as opposed to do a summation, but XL can handle doing sums lightning fast, even if you give it a million cells, so I wouldn't worry about that factor.