基于两个列值的VLOOKUP [英] VLOOKUP based on two column values

问题描述

我有一列带有用户ID的表.多次输入这些内容以显示特定主题的结果.

I have a table with User IDs in one column. These are entered multiple times to show the result for a particular topic.

ID          Topic       Pass/Fail
71086686    Science     P
71086686    Maths       P
71086686    Tech        P
71086686    ICT         F
71086687    Science     P
71086687    Maths       P
71086687    Tech        P
71086687    ICT         F

我正在尝试找到一种方法来返回特定ID和主题的通过/失败结果.

I am trying to find a way to return the Pass/Fail result for a particular ID and topic.

我正在尝试使用VLOOKUP来执行此操作，但无法弄清楚如何使用多列作为要查找的值.

I am trying to use VLOOKUP to do this but cant work out how to use multiple columns as values to look up against.

基本上，我正在尝试实现以下目标:

essentially i am trying to acheive the below:

Vloookup([ID] and [Topic], A:C, 3, FALSE)

推荐答案

当您可以使用INDEX& ;; MATCH组合.

I don't recommend to concatenate criteria and searchranges when you could use INDEX & MATCH combo.

=INDEX(C2:C9,MATCH(1,(A2:A9=Criteria1)*(B2:B9=Criteria2),0))

并通过 Ctrl Shift Enter

如果要删除输入的数组，请执行另一个INDEX:

Implement another INDEX if you want to remove the array entering:

=INDEX(C2:C9,MATCH(1,INDEX((A2:A9=Criteria1)*(B2:B9=Criteria2),0),0))

这篇关于基于两个列值的VLOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！