Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
select multiple criteria multiple worksheets
I have two worksheets along the lines of:
SHEET 1 SHEET 2 A B C D A B C D 1 1 10 100 1 10 100 1 3 20 - 1 20 - 2 6 20 300 1 30 200 2 9 30 200 2 20 300 2 9 30 200 3 10 100 3 11 10 100 3 10 200 3 11 10 200 I want to fill incolumn "B" on Sheet 2 with the values from column B in sheet 1 whereever columns A, C, and D match. Any ideas on how to do this? thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
select multiple criteria multiple worksheets
In Sheet2,
Put in B1, array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF(ISNA(MATCH(1,(Sheet1!$A$1:$A$10=A1)*(Sheet1!$C $1:$C$10=C1)*(Sheet1!$D$1:$D$10=D1),0)),"",INDEX(S heet1!$B$1:$B$10,MATCH(1,(Sheet1!$A$1:$A$10=A1)*(S heet1!$C$1:$C$10=C1)*(Sheet1!$D$1:$D$10=D1),0))) Copy down. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "koneil" wrote: I have two worksheets along the lines of: SHEET 1 SHEET 2 A B C D A B C D 1 1 10 100 1 10 100 1 3 20 - 1 20 - 2 6 20 300 1 30 200 2 9 30 200 2 20 300 2 9 30 200 3 10 100 3 11 10 100 3 10 200 3 11 10 200 I want to fill incolumn "B" on Sheet 2 with the values from column B in sheet 1 whereever columns A, C, and D match. Any ideas on how to do this? thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup for multiple criteria, multiple worksheets | Excel Worksheet Functions | |||
select lines from multiple worksheets and write to new worksheet | Excel Worksheet Functions | |||
Select by multiple criteria (Excel 2003) | Excel Worksheet Functions | |||
Select rows of data in a worksheet on one criteria in multiple co | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |