vlookup two values and return another one

[deep_101]

Hi All,

First time posting here and im not that familiar with VBA.

I have two workbooks which are data dumps from another program. Say I have WO #, Task #, and Cost in workbook1 and WO #, Task # in workbook2. I need to compare the WO# in workbook2 with WO# in workbook1 if it matches I need to compare the Task # in workbook2 with the Task# in workbook1 (but it must compare the same row in which the WO # match was found) then it must return the cost from workbook1 to workbook2 ( again the cost must come from the same row in which the WO and Task match were found in).

This could be a VBA code or even a formula that does not matter.

Your help would be appreciated.

Thank you.

 

[jbeaucaire]

Late to the party I know, but since SUMPRODUCT() formulas are problematic in large quantities, I thought I'd point out the INDEX/MATCH alternative for this scenario. INDEX/MATCH is the most robust tool for lookups and worth using every chance you get.

Most people abandon them when there is a two- or three-criteria to the match, but you don't have to. Something like this works fine:

=INDEX([wb2]sheet1!C$1:C$1000, MATCH( A2 & B2, INDEX([wb2]sheet1!A$1:A$1000 & [wb2]sheet1!B$1:B$1000,0),0))

If there is a chance of some odd duplication (if the values are numeric and small, this is very possible), then add a separator, like so:

=INDEX([wb2]sheet1!C$1:C$1000, MATCH( A2 & "-" & B2, INDEX([wb2]sheet1!A$1:A$1000 & "-" & [wb2]sheet1!B$1:B$1000,0),0))