ExcelBanter - select multiple criteria multiple worksheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - select multiple criteria multiple worksheets (https://www.excelbanter.com/excel-worksheet-functions/169434-select-multiple-criteria-multiple-worksheets.html)

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.

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.