CODEWITHSUNDEEP
excel
Aron Simpson
Aron Simpson

Reputation: 3

In Excel, compare two arrays for multiple criteria then return a different value

I have two arrays. The first contains the rates for different jobs from different companies:

Task:             Contractor:   Pay rate:
Labour            Company A     25
Excavation        Company A     28
Labour            Company B     22
Traffic Control   Company B     24
Labour            Company C     26
Excavation        Company C     26

The second is where we input the work done by each company:

Task:             Contractor:   Hours:   Rate:   Total:     
Labour            Company B     18      
Excavation        Company C     8

If I use VLOOKUP, INDEX(MATCH) etc., it always only checks against the first match it finds, so if I were to look up "Labour" it would only check against the first labour listing and not the labour listings for Company B or Company C.

Is there a way to have Excel match BOTH the "task" and "contractor" value, then return the "pay rate" value?

Upvotes: 0

Views: 2617

Answers (2)

rwilson
rwilson

Reputation: 2145

As long as each combination of task and Contractor appear only once in your data, you could do a simple SUMIFS:

=SUMIFS($C$2:$C$7,$B$2:$B$7,B10,$A$2:$A$7,A10)

enter image description here

Upvotes: 1

user4039065
user4039065

Reputation:

Try this in the Sheet2 D2,

=INDEX(Sheet1!$C$1:$C$999, AGGREGATE(15, 6, ROW($1:$999)/((Sheet1!$A$1:$A$999=$A2)*(Sheet1!$B$1:$B$999=$B2)), 1))

This is a standard two column match. For your purposes (with unique combinations) a SUMIFS function would also have provided the correct Pay Rate.

Upvotes: 0

Related Questions