select multiple criteria multiple worksheets

  • Thread starter Thread starter koneil
  • Start date Start date
K

koneil

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.
 
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(Sheet1!$B$1:$B$10,MATCH(1,(Sheet1!$A$1:$A$10=A1)*(Sheet1!$C$1:$C$10=C1)*(Sheet1!$D$1:$D$10=D1),0)))
Copy down. Adapt the ranges to suit.
 
Back
Top