search value in 2 columns return value in another
 

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?

search value in 2 columns return value in another
 

Try this:

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

--
Biff
Microsoft Excel MVP


"jmegdan1" wrote in message
...
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?

search value in 2 columns return value in another
 

Then copy down as needed

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

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

--
Biff
Microsoft Excel MVP


"jmegdan1" wrote in message
...
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?

search value in 2 columns return value in another
 

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!A1:D5,match(A2&B2,sheet2!D1:D5,0),3)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"jmegdan1" wrote in message
...
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?