Excel find value which satisfies values from another two columns

0
votes
col1       col2     col3

26.03.2020 163,35    a
27.03.2020 153       a 
02.04.2020 153,12    v 
29.03.2020 153,12    b 
30.03.2020 141       a 
31.03.2020 142       v 
01.04.2020 137       c 
02.04.2020 168       a 
03.04.2020 185       a 
02.04.2020 185       t 
05.04.2020 185       y

In a cell I want a value that shows col2 value where col1 = 02.04.2020 and col3= a, so I need to look up for col1 = 02.04.2020 (say G1) and col3= a (say G5).

I tried

=INDEX(col2; MATCH(1;(G1=col1)*(G5=col3),0)

However, got NA.

1
excelexcel-formula
Can there be duplicates of for example 01.04.2020 and c? And if so, should all results be listed? – OverflowStacker
@johnmayer Your formula works. Just enter it as array formula with ctrl + shift + enter – basic

1 Answers

2
votes

Instead of array entered formula try:

=SUMIFS(B:B,A:A,G1,C:C,G5)

Other variants could be MAXIFS or MINIFS if one has access.