Forum Discussion
Question about match formulas
=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.
=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.
- 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!
