INDEX MATCH 2 columns multiple rows

Question

I've been trying to use the INDEX MATCH formula for the following table, to display the desired value in cell K6. I've been able to get the correct cell to display in K6 as long as K3 = Result1. However, when I try to populate K3 = any other cell between D2:H2, I receive the #REF error in K6. Please help! I'm not sure why it won't populate.

Snapshot of example table

How about updating your formula to the question?

Gowtham Shiva
– Gowtham Shiva

2017-06-09 15:58:49 +00:00
Commented Jun 9, 2017 at 15:58 — Gowtham Shiva
– Gowtham Shiva, Commented Jun 9, 2017 at 15:58

Scott Craner · Accepted Answer · 2017-06-09 16:06:51Z

1

Use this formula:

=INDEX(C:H,AGGREGATE(15,6,ROW($A$3:$A$14)/(($A$3:$A$14=K3)*($B$3:$B$14=K4)),1),MATCH(K5,$C$2:$H$2,0))

answered Jun 9, 2017 at 16:06

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

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

2 Comments

Brittany Over a year ago

This is perfect. Thank you!

Scott Craner Over a year ago

@Brittany please mark as correct by clicking the check mark by the answer.

Collectives™ on Stack Overflow

INDEX MATCH 2 columns multiple rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related