在Excel中,比较两个数组的多个条件,然后返回一个不同的值 [英] In Excel, compare two arrays for multiple criteria then return a different value
问题描述
任务:承包商:支付率:
劳工公司A 25
挖掘公司A 28
劳工公司B 22
交通控制公司B 24
劳工公司C 26
挖掘公司C 26
第二个是我们输入每个公司完成的工作:
任务:承包商:小时:价格:总计:
劳工公司B 18
挖掘公司C 8
如果我使用VLOOKUP,INDEX(MATCH)等,它总是检查它找到的第一个匹配,所以如果我要查找劳工,它只会检查对于第一个劳动力上市而不是公司B或公司C的劳动力上市。
有没有办法让Excel匹配任务和承包商价值,然后返回支付率值?
在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))
这是一个标准的两列匹配。为了您的目的(独特组合) SUMIFS功能也将提供正确的支付率。
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?
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.
这篇关于在Excel中,比较两个数组的多个条件,然后返回一个不同的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!