Populate asyncronous data

[BRISBANEBOB]

I'm trying to work out a method (formula if possible, otherwise VBA if not) to populate date from one table to another where the data is not in similar table formats. Sample attached for any ideas.

Thanks

[zbor]

Try:

=SUMPRODUCT(--(Sheet2!$A$6:$A$18=Sheet1!$A5);--(Sheet2!$B$6:$B$18=B$4);Sheet2!$C$6:$C$18)

[JBeaucaire]

I would add a lookup key in column D to the data itself then use a simple INDEX/MATCH. Without the key you would probably need an array formula or SUMPRODUCT, both of which slow your sheet performance in large numbers.

=IF(ISERROR(MATCH($A5&B$4,Sheet2!$D$6:$D$18,0)),"",INDEX(Sheet2!$C$6:$C$18,MATCH($A5&B$4,Sheet2!$D$6:$D$18,0)))

If you install the IFERROR UDF into your sheet, you can even simplify further:
=IFERROR(INDEX(Sheet2!$C$6:$C$18,MATCH($A5&B$4,Sheet2!$D$6:$D$18,0)),"")

IFERROR UDF code:

Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
    If IsArray(ToEvaluate) Then
        IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
    Else
        IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
    End If
End Function

Even without the UDF, INDEX/MATCH is fast fast for this kind of stuff, even in large numbers.

[BRISBANEBOB]

Both solutions work a treat - thanks.

With the =SUMPRODUCT solution I replaced the ";" with a comma to get it to work. Why do we use a comma? What does it do? WHy not an * ?

With the INDEX/MATCH solution, what is the IFERROR UDF? I assume UDF is User Defined Code??

Thansk for the help.

[JBeaucaire]

The INDEX/MATCH will return an error if the MATCH value isn't found. To test for that, you have to verify the MATCH is going to work before you use it. IF(ISERROR() does that, but if it passes the test, you have to do the formula again to get the actual answer.

Honestly, even doing a double evaluation like that, it's still less plumbing on your sheet than array formulas or tons of SUMPRODUCT which are only marginally better, IMO.

In Excel 2007 they solved that by offering you the IFERROR option. IFERROR let's you just run the formula once...if it works you get the normal answer, but if an error occurs, it stifles the error and returns your default value instead, or lets you even run a different formula...up to you.

=IFERROR(YourFormula,DoThisOnError)

To install the IFERROR code into earlier versions of Excel:

Press ALT-F11 to open the VBEditor
Click Insert > Module
Paste in the code
Alt-F11 to close the editor
Save your sheet.

Now you can use that function as shown.

Your sample sheet indicates a LOT of cells. I encourage you to consider all your alternatives based on warnings already given.

[BRISBANEBOB]

Thanks for that explanation - the learning process continues!