If statement looking for either/and

[katiedee]

The following formula is not returning all the possible results, but I know I'm overlooking something very simple. Appreciate any help!

Worksheet 'Company One' has column A: with four possible choices. I only want to find the cells that have "yes" and "no." Column B: has company names. I only want to find cells wth "Rib D". Column C: has client names.

Second worksheet 'Results', is to list horizontally starting at C33, all the client names that are with Rib D AND have either "Yes" or "No."

The formula below is what I have in Results C33 and across. It only returns the very first match, but no other subsequent matches:

[FONT=Calibri][SIZE=3][COLOR=#000000]=IFERROR(INDEX('company one'!c4:c83,AGGREGATE(15,6,(ROW('companyone'!c4:c83)-ROW('company one'!c4)+1)/('companyone'!a4:a83="yes")*OR('companyone'!a4:a83="no")*AND('company one'!b4:b83="RibD"),COLUMNS($C33:c33))),"")[/COLOR][/SIZE][/FONT]

 

[Marcelo Branco]

Column B should be RibD or Rib D?
Assuming the later, try
C33 copied across
=IFERROR(INDEX('Company One'!$C$4:$C$83,AGGREGATE(15,6,(ROW('Company One'!$C$4:$C$83)-ROW('Company One'!$C$4)+1)/((('Company One'!$A$4:$A$83="no")+('Company One'!$A$4:$A$83="yes"))*('Company One'!$B$4:$B$83="Rib D")),COLUMNS($C33:C33))),"")

Hope this helps

M.

 

[SeanKC1989]

The following formula is not returning all the possible results, but I know I'm overlooking something very simple. Appreciate any help!

Worksheet 'Company One' has column A: with four possible choices. I only want to find the cells that have "yes" and "no." Column B: has company names. I only want to find cells wth "Rib D". Column C: has client names.

Second worksheet 'Results', is to list horizontally starting at C33, all the client names that are with Rib D AND have either "Yes" or "No."

The formula below is what I have in Results C33 and across. It only returns the very first match, but no other subsequent matches:

[FONT=Calibri][SIZE=3][COLOR=#000000]=IFERROR(INDEX('company one'!c4:c83,AGGREGATE(15,6,(ROW('companyone'!c4:c83)-ROW('company one'!c4)+1)/('companyone'!a4:a83="yes")*OR('companyone'!a4:a83="no")*AND('company one'!b4:b83="RibD"),COLUMNS($C33:c33))),"")[/COLOR][/SIZE][/FONT]

[COLOR=#006FE0 !important]=[COLOR=#800080 !important]IF[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#004ED0 !important]ISERROR[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#004ED0 !important]INDEX[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]A[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#006FE0 !important]:[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]B[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]8[/COLOR][COLOR=#333333 !important],[/COLOR][COLOR=#004ED0 !important]SMALL[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#800080 !important]IF[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]A[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#006FE0 !important]:[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]A[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]8[/COLOR][COLOR=#006FE0 !important]=[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]E[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#333333 !important],[/COLOR][COLOR=#004ED0 !important]ROW[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]A[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#006FE0 !important]:[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]A[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]8[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important],[/COLOR][COLOR=#004ED0 !important]ROW[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#006FE0 !important]:[/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important],[/COLOR][COLOR=#CE0000 !important]2[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important],[/COLOR][COLOR=#008000 !important]""[/COLOR][COLOR=#333333 !important],[/COLOR][COLOR=#004ED0 !important]INDEX[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]A[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#006FE0 !important]:[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]B[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]8[/COLOR][COLOR=#333333 !important],[/COLOR][COLOR=#004ED0 !important]SMALL[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#800080 !important]IF[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]A[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#006FE0 !important]:[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]A[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]8[/COLOR][COLOR=#006FE0 !important]=[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]E[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#333333 !important],[/COLOR][COLOR=#004ED0 !important]ROW[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]A[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#006FE0 !important]:[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#002D7A !important]A[/COLOR][COLOR=#333333 !important]$[/COLOR][COLOR=#CE0000 !important]8[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important],[/COLOR][COLOR=#004ED0 !important]ROW[/COLOR][COLOR=#333333 !important]([/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#006FE0 !important]:[/COLOR][COLOR=#CE0000 !important]1[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important],[/COLOR][COLOR=#CE0000 !important]2[/COLOR][COLOR=#333333 !important])[/COLOR][COLOR=#333333 !important])[/COLOR]
[/COLOR]
[FONT=&quot]MALL() section of the Array Formula identifies the row numbers that match your selection. It uses the row number to return the value in that row only. When you copy the formula down, it automatically jumps to the next matching row number and returns that value, until it can’t find anything and errors out (resulting in a blank cell).

borrowed this from eimagine. Thought I would share as lokoing for exactly the same thing[/FONT]

 

[katiedee]

Sorry, for the late response, but yes, it works exactly as I needed! In the interest of time I went ahead and created two separate formulas - one for "no" and one for "yes" to return results on different rows. But my coworker was very excited to hear there was a way to give just one row of names as she is the end user. Thank you!!!

 

[katiedee]

Thank you for your help as this is exactly what I needed. I realized my other reply did not include your response, but did want you to know how much this helps.

Column B should be RibD or Rib D?
Assuming the later, try
C33 copied across
=IFERROR(INDEX('Company One'!$C$4:$C$83,AGGREGATE(15,6,(ROW('Company One'!$C$4:$C$83)-ROW('Company One'!$C$4)+1)/((('Company One'!$A$4:$A$83="no")+('Company One'!$A$4:$A$83="yes"))*('Company One'!$B$4:$B$83="Rib D")),COLUMNS($C33:C33))),"")

Hope this helps

M.