Adding a second condition to an existing formula

[ZedaG]

Hello all,

I have a file that has a master list of staff. From the master list I have created multiple tabs that subsets the master list into teams of staff depending on their team membership. For example, the master list will have staff name and then team as seen in the list below and then I have tab (Ad) and tab (Sales). I use a formula (see below) to pull that name into the correct tab (Sales or Ad, etc). The condition is always team membership. Now I need to create another condition – if contract = Yes. I tried an IFS and then adding ‘Master list'!$C$1:$C$500="Yes", but that didn’t work. How do I modify the formula below to pullover the appropriate name based on two conditions?

Name Team Contract
Frank Holzt Ad No
Miriam Turner Sales Yes


=IF(ISERROR(INDEX('Master list'!$A$1:$D$500,SMALL(IF('Master list'!$B$1:$B$500="Sales", ROW('Master list'!$B$1:$B$500)),ROW(1:1)),2)),"",INDEX('Master list'!$A$1:$D$500,SMALL(IF('Master list'!$B$1:$B$500="Sales", ROW('Master list'!$B$1:$B$500)),ROW(1:1)),1))

Thanks for your help.

Zeda

[crushdrinker06]

have you tried the AND formula? example IF(AND(A1=1,B2=1),C1=1,C1=2) this is how too do both. there is also an OR formula that can work in place of the AND formula.

[Tony Valko]

What type of data does the formula return? Is it text? Numbers? Could be both? Something else?

[ZedaG]

What type of data does the formula return? Is it text? Numbers? Could be both? Something else?

Mr. Valko,

It returns either, it depends on the cell it is referencing.

Zeda

[ZedaG]

have you tried the AND formula? example IF(AND(A1=1,B2=1),C1=1,C1=2) this is how too do both. there is also an OR formula that can work in place of the AND formula.

Crushdrinker06:

I never thought of the AND formula. I tried it and it works well. I can narrow down more specifically which staff I'm pulling into the specific team tabs. I believe I can also use the OR. This makes a lot of sense.

Thanks to both your and Mr. Valko for your help.

Zeda

[Tony Valko]

=IF(ISERROR(INDEX('Master list'!$A$1:$D$500,SMALL(IF('Master list'!$B$1:$B$500="Sales", ROW('Master list'!$B$1:$B$500)),ROW(1:1)),2)),"",INDEX('Master list'!$A$1:$D$500,SMALL(IF('Master list'!$B$1:$B$500="Sales", ROW('Master list'!$B$1:$B$500)),ROW(1:1)),1))

I'm a bit confused by your formula.

The error trap portion is testing column 2 but the value_if_false portion is testing column 1.

[Tony Valko]

Here's an example of what you want to do based on using Excel 2003.

We want to extract data from column A where column B = X AND column C = X.

Data Range

	A	B	C	D	E
1	Header1	Header2	Header3	------	Header4
2	Data1	X	C		Data3
3	Data2	C	X		Data8
4	Data3	X	X
5	Data4	D	X
6	Data5	X	C
7	Data6	S	X
8	Data7	A	C
9	Data8	X	X
10	Data9	X	D

This array formula** entered in E2:

=IF(ISERROR(SMALL(IF((B$2:B$10="X")*(C$2:C$10="X"),ROW(A$2:A$10)),ROWS(E$2:E2))),"",INDEX(A:A,SMALL(IF((B$2:B$10="X")*(C$2:C$10="X"),ROW(A$2:A$10)),ROWS(E$2:E2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.