Excel find value which satisfies values from another two columns

Question

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.

Can there be duplicates of for example 01.04.2020 and c? And if so, should all results be listed? — OverflowStacker
– OverflowStacker, Commented May 7, 2020 at 11:09
@johnmayer Your formula works. Just enter it as array formula with ctrl + shift + enter — basic
– basic, Commented May 7, 2020 at 11:37

JvdV · Accepted Answer · 2020-05-07 11:49:43Z

2

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.

edited May 7, 2020 at 11:49

answered May 7, 2020 at 11:12

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel find value which satisfies values from another two columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related