| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search for value on another sheet, return Y/N | Excel Discussion (Misc queries) | |||
| Search table for string return next columns value | Excel Worksheet Functions | |||
| Search and return | Excel Worksheet Functions | |||
| Search Multiple columns for criterion asterisk (*) and Return Numeric Label | Excel Worksheet Functions | |||
| search for and return more than one row in a sheet | Excel Worksheet Functions | |||
Linear Mode
