# Excel Formula Multiple Criteria Lookup

Copper Contributor

# Excel Formula Multiple Criteria Lookup

I need a formula that says if this value in column A Sheet 1 equals a value in Column A Sheet 2, and the value in column B Sheet 1 equals a value in Column B Sheet 2 then return the value in Column C Sheet 2.

3 Replies

# Re: Excel Formula Multiple Criteria Lookup

``=VLOOKUP(A1&B1,CHOOSE({1,2},Sheet2!\$A\$1:\$A\$1000&Sheet2!\$B\$1:\$B\$1000,Sheet2!\$C\$1:\$C\$1000),2,0)``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

# Re: Excel Formula Multiple Criteria Lookup

Your question is open to multiple interpretations.

Taking the simplest interpretation, put this formula in Sheet 1, Cell C1

=IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1),Sheet2!C1,"No match")

But what you wrote:

I need a formula that says if this value in column A Sheet 1 equals a value in Column A Sheet 2, and the value in column B Sheet 1 equals a value in Column B Sheet 2 then ...

also could be interpreted to mean if a value anywhere in column A of sheet 1 equals a value anywhere in column A of Sheet 2...

# Re: Excel Formula Multiple Criteria Lookup

``=INDEX(Sheet2!\$C\$1:\$C\$1000,MATCH(1,(Sheet1!A1=Sheet2!\$A\$1:\$A\$1000)*(Sheet1!B1=Sheet2!\$B\$1:\$B\$1000),0))``

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.