Excel VLOOKUP with Multiple Criteria in Horizontal & Vertical Way: 5 Methods

Method 1 – Use a Helper Column to Left to Match Multiple Criteria with VLOOKUP

Steps:

Create a helper column on the left side of your dataset.

Helper Column

Click on cell B5.
Insert the following formula with AND function and hit Enter.

=AND(C5=$D$16,D5=$D$17)

Using AND Function to Get Helper Column Value

Place your cursor in the bottom right position of cell B5.
A black fill handle will appear.
Drag the fill handle below to copy the same formula for all the cells below.

Use Fill Handle Feature

You will get all the helper column data.

Helper Column Data

Click on cell D18 and insert the following formula.

=VLOOKUP(TRUE,B5:E14,4,FALSE)

Press Enter.

Using VLOOKUP Function to Vlookup Multiple Criteria in Horizontal and Vertical Direction

You will be able to look up your desired data in the dataset with multiple criteria horizontally and vertically.

Method 2 – Apply VLOOKUP Function with Multiple Criteria Using Ampersand (&) Operator with Helper Column

Steps:

Click on cell B5 and insert the following formula in your helper column cell.

=C5&D5

Press Enter.

Using Ampersand (&) Operator to Get Helper Column Data

Place your cursor in the bottom right position of cell B5 and drag the black fill handle downward to copy the same formula for all the cells below.

Fill Handle Feature

You will get all the helper column data that fits your needs.

Helper Column Values

Click on cell D18 and insert the formula below.

=VLOOKUP(D16&D17,B5:E14,4,FALSE)

Press the Enter button.

Using VLOOKUP Function to Find Multiple Criteria in Horizontal and Vertical Direction

You will get your desired person’s department looked up.

Method 3 – Use Helper Row and Combine TRANSPOSE Function with VLOOKUP for Horizontal Lookup

Steps:

At the Helper Row, click on cell C4.
Insert the formula below and press Enter.

=C5&C6

Use Ampersand (&) Operator to Get Helper Row Data

Place your cursor in the bottom right position of cell C4.
Drag the fill handle rightward upon its arrival.

Fill Handle Feature

You will get all the data of the helper row.

Helper Row Data

Click on cell C11 and insert the following formula.

=VLOOKUP(C9&C10,TRANSPOSE(C4:L7),4,FALSE)

Hit the Enter button.

Combine VLOOKUP & TRANSPOSE Functions Lookup Multiple Criteria Horizontally

You will get the department for Peter Parker.

How to VLOOKUP for Horizontal and Vertical Lookup with Multiple Criteria in Excel: 2 Alternative Formulas

Method 1 – INDEX-MATCH Formula for Vertical and Horizontal Lookup with Multiple Criteria

Steps:

Click on cell C18 and insert the following formula.

=INDEX(D5:D14,MATCH(1,(B5:B14=C16)*(C5:C14=C17),0))

Formula Breakdown:

MATCH(1,(B5:B14=C16)*(C5:C14=C17)

This function will return the row index number where the C16 cell’s value is in the B5:B14 range and the C17 cell’s value is in the C5:C14 range.
Result: 8