Question about match formulas

[Rich89]

Hello!

A long time ago someone solved a question here regarding looking for matches in Excel.  I'm wanting to have an explanation of how this works.  With a formula like this:

=MATCH(A2&B2,C:C&D:D,0)

Excel will look for where A2 and B2 match against C and D.  So, if it finds the values in A5 and B5 in C120 and D120, this will report a match.

I just want an explanation of why this works.  How does it know to look for A2 in C and B2 in D and also confirm they are in the same relative position (so it won't match if its found in C3 and D50).  Can someone explain this?

I'd also like to know how to get a formula like this to work with an IF formula without returning N/A where no match is found.  For now I've just wrapped it in IFERROR(IF) but I'm sure there is a better way.

[#2 · Accepted solution]

@Rich89 

=IFNA(MATCH(A2&"_"&B2,C1:C10&"_"&D1:D10,0),"")

You can apply IFNA in order to return an empty cell if no match is found.

MATCH formula creates an array of all combined cells C1 and D1 , C2 and D2 and so on (not cells C7 and D20 or C11 and D26) to compare values from A2 and B2.

This is shown in the attached file. You can select C1:C10&"_"&D1:D10 in the formula and press F9 in order to view the array. To return press crtl+z.

It's advisable to enter A2&"_"&B2 (with "_" for example). Otherwise if A2 and B2 were 111 and 1 and cells C10 and D10 were 11 and 11, MATCH would return that values are found. However this would be wrong.

[#3]

This is great! Thank you for that explanation. I didn't know about using the character to separate the values or why either, so I am glad I asked. On the occasion that I needed to use this, I was matching text and a number to text and a number, so it should be fine. However, I will make that a best practice now.

Very much appreciate your reply!