Returning row value based on multiple criterias in array (not tables). IF ? Lookup ?

[KomicJ]

This would be easy to figure out in a table, but I can't use one for this particular project (document attached to this post).

Basically, I'm trying to go through many different game results (cells B18:i196), finding if two teams played against each others, and return the result in the appropriate cell.

ie. Let's take cell Z8, so "Grenier" against "Legault". I need to go through row 18 through row 196, and find the row that has $B8 in column B, Z$4 in column C, and then return the value of column D (rec) from that specific line. Then copy/paste that formula all over U5:AD14. I think I know how I would do that in a table, but for some reasons, can't figure out how to do it in current sheet format.

Anyone willing to help will earn my eternal gratitude.

[KomicJ]

Here's what I tried, but didn't work:

=IF($B8=Z$4,"-",INDEX($D$18:$D$196,MATCH($B8&Z$4,$B$18:$B$196&$C$18:$C$196,0)))

Edit: Nevermind, only forgot to enter as an array formula, my bad.

[Tony Valko]

Here's another way to do it. Range concatenation is inefficient (slow to calculate).

Array entered**:

=IF($B8=Z$4,"-",INDEX($D$18:$D$196,MATCH(1,($B$18:$B$196=$B8)*($C$18:$C$196=Z$4),0)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.