CODEWITHSUNDEEP
excelexcel-2010
daniell
daniell

Reputation: 179

How to use index and match with two columns?

I have the following caseenter image description here

E.g., I want the two cells in yellow to be the same. For this, I need to find the columns Score 1 and Result, and then find the row 03-Jan so that I get the actual score. Do you have any idea how to solve this? I tried with some match and index but I do not get the solution.

Upvotes: 0

Views: 2095

Answers (2)

RazorSky
RazorSky

Reputation: 360

You want to use an Index(Match),Match())

but what you really want to use is a double XLOOKUP. enter image description here

I also liked this video to help me learn all of the ways to do a 2d lookup. YouTube Video

Upvotes: -1

Scott Craner
Scott Craner

Reputation: 152450

Use INDEX with three matches, the first to find the correct row, while the other 2 find the correct column.

=INDEX($E:$N,MATCH($Q9,B:B,0),MATCH(R$8,$E$2:$N$2,0)+MATCH(R$7,$E$3:$I$3,0)-1)

Upvotes: 2

Related Questions