Index Match formula with 2 matches

[willow1985]

Hello,

I was wondering if someone could help me with a formula.

I frequently use the following Index Match formula:

=INDEX(P:P,MATCH(C2,O:O,FALSE))

if C2 matches a value in column O it will return the corresponding value in P.

What I would like to do is have 2 matches.

return the value in P if C2 matches the value in O AND if I2 matches the value in column Q,

aka =INDEX(P:P,MATCH(C2,O:O,FALSE) AND MATCH(I2,Q:Q,FALSE)

Is there a way to do this?

Thank you to anyone who can help

Carla

 

[Fluff]

How about
-index(P:P,match(C2&"|"&I2,index(O:O&"|"&Q:Q,0),0))

 

[willow1985]

It turns out I need 3 matches, but I modified your formula to account for this and it appears to be working great!

=INDEX(P:P,MATCH(C3&"|"&I3&"|"&A3,INDEX(O:O&"|"&Q:Q&"|"&N:N,0),0))

Thank you very much!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[willow1985]

Out of curiosity, what does "|" mean?
I have never seen this in a formula before

 

[Eric W]

As a side note, if you're trying to return a number, then you can use SUMIFS like so:

=SUMIFS(P:P,O:O,C3,Q:Q,I3,N:N,A3)

 

[Fluff]

Out of curiosity, what does "|" mean?

It's simply a separator to ensure that you don't get incorrect results.
with this sample & no separator, they would all be considered the same value

+Fluff New.xlsm
	A	B	C	D
1			Separator	No separator
2	abc	def	abc|def	abcdef
3	ab	cdef	ab|cdef	abcdef
4	abcd	ef	abcd|ef	abcdef
Main

Cell Formulas
Range	Formula
C2:C4	C2	=A2&"|"&B2
D2:D4	D2	=A2&B2

 

[jbesclapez]

Guys, you can now use the formula XLOOKUP in the latest office 365. Easier and nicer than Index Match as it also has more options... please look at it

XLOOKUP function - Microsoft Support

Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from...

support.office.com

 

[willow1985]

It's simply a separator to ensure that you don't get incorrect results.
with this sample & no separator, they would all be considered the same value

+Fluff New.xlsm
	A	B	C	D
1			Separator	No separator
2	abc	def	abc|def	abcdef
3	ab	cdef	ab|cdef	abcdef
4	abcd	ef	abcd|ef	abcdef
Main

Cell Formulas
Range	Formula
C2:C4	C2	=A2&"|"&B2
D2:D4	D2	=A2&B2

So in relation to my formula, without the separator it would combine all the data from the 3 cells and look for it as a group?
Am I understanding this correct?

Thank you so much for the brief lesson, I have a lot to learn.

I memorize formulas and VBA code, know which ones do what but I lack a full understanding of how some of them work

 

[Fluff]

So in relation to my formula, without the separator it would combine all the data from the 3 cells and look for it as a group?

Yes that's right.