lookup meeting two criteria

[sick stigma]

in the attached workbook I need a formula to match a value from column A (one) and a value from column B (two) and return value in column D (four). Values are listed in A2:B2 with returned value in C2.
Any help is appreciated.
Sick

[mcmahobt]

=INDEX(D7:D22,MATCH(A2&B2,A7:A22&B7:B22,0))

Confirmed with CTRL+SHIFT+ENTER as an array formula.

[Richard Buttrey]

Hi,

Often (as here) it's just simpler to use a helper column (which you can hide if you wish)

See attached

[sick stigma]

I'm trying your first response but get a #value! reply. I'm entering as an array. Suggestions?

[Richard Buttrey]

I'm trying your first response but get a #value! reply. I'm entering as an array. Suggestions?

To whom is that addressed?

[sick stigma]

sorry, mcmahobt

[sick stigma]

Here is the actual workbook. I've cleared all sensitive information. The array is N66:S138. Value cells are C11&D11. Target cell is J11.
Suggestions?
Sick

[mcmahobt]

Weird, I'm not seeing a value error if it is indeed entered as an array. See attached for implemented solution.

[mcmahobt]

Yeah, this is much different than the sample you provided. Which column from your array houses the data you wish to populate J11?

[sick stigma]

Column R.
S in another cell down the road.
Sick

[mcmahobt]

I'm assuming C11 and D11 correspond to columns O and A respectively in your array. If that is the case, there is no Item 1 Promo J match. This formula should still work for other combinations that do appear however:

=INDEX($R$66:$R$138,MATCH(C11&D11,$O$66:$O$138&$P$66:$P$138,0))

again, entered as an array with CSE.

[sick stigma]

Nope. #value!. Does a specific column in the arrary have to be sorted?

[Czeslaw]

I propose another formula.

=LOOKUP(2;1/(A6:D6=A1)/(A7:A22=A2)*(A6:D6=B1)/(B7:B22=B2)*(A6:D6=C1);D7:D21)