CODEWITHSUNDEEP
excelexcel-formula
John Mayer
John Mayer

Reputation: 113

Excel find value which satisfies values from another two columns

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.

Upvotes: 0

Views: 39

Answers (1)

JvdV
JvdV

Reputation: 75840

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.

Upvotes: 2

Related Questions