search value in 2 columns return value in another

[jmegdan1]

2 worksheets

Sheet 1
Area Sector Year
Berkley Retail (BLANK)
Berkley Office (BLANK)
Clawson Retail (BLANK)
Clawson Office (BLANK)


Sheet 2
Area Sector Year
Berkley Retail 1
Clawson Retail 4
Clawson Office 3
Berkley Office 2


How do I get the Year value in sheet 2 to populate into sheet 1 based on 2
matched criteria?

 

[T. Valko]

Try this:

=SUMPRODUCT(--(Sheet2!A$2:A$5=A2),--(Sheet2!B$2:B$5=B2),Sheet2!C$2:C$5)

 

[T. Valko]

Then copy down as needed

 

[Ashish Mathur]

Hi,

You can also use the INDEX and MATCH function here

In column D of sheet 2, enter the following formula in D2 =A2&B2. In C2 of
sheet1, enter the following formula

=index(sheet2!A15,match(A2&B2,sheet2!D15,0),3)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com