Author: Oscar Cronquist Article last updated on November 15, 2018

The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula if possible, see above picture.

=INDEX(D3:D10,MATCH(B13&C13,INDEX(B3:B10&C3:C10,),0))

It combines both values you want to look for using the ampersand character.

B13&C13

Then it concatenates the two cell ranges also using the ampersand character, the INDEX function makes it a regular formula.

INDEX(B3:B10&C3:C10,)

The MATCH function then returns the relative position of the of the combined values, see picture above.

MATCH(B13&C13,INDEX(B3:B10&C3:C10,),0) returns 6. The value is in the 6th position in the array.

INDEX(D3:D10,MATCH(B13&C13,INDEX(B3:B10&C3:C10,),0))

becomes

INDEX(D3:D10, 6)

and returns Jennifer in cell D13.

If you don't mind array formulas, the only advantage is that it is somewhat smaller, use this formula:

=INDEX(D3:D10,MATCH(B13&C13,B3:B10&C3:C10,0))

To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.