Google Sheets Return value when two other parameters are met.

[BadFish523]

Looking for more help. You guys have always helped me perfectly in the past. I have a large table with about 5 columns. I'm looking for a formula that will check if column 4=yes and column 5 =no in the same row then display column b from the same row. Make sense? Hope someone can help?

 

[Aladin Akyurek]

Something like

=IF(D2="yes",IF(E2="no",B2,"")

which you can copy down.

 

[BadFish523]

That will work. I should have been more specific though. The list is very long and only a few will meet the criteria. I'd rather not have a long list with blanks that don't meet the criteria. Anyway to meet the criteria but ignore those that don't meet the criteria? Basically out of my long list I would only like to list the ones that meet the criteria. Trying to highlight some problem areas in a small clean table? Also the data is fed by a Google form so my list of data will continue to grow.

 

[BadFish523]

Also this is only returning a blank cell weather the criteria is met or not?

 

[Aladin Akyurek]

Also this is only returning a blank cell weather the criteria is met or not?

=IF(D2="yes",IF(E2="no",B2,""),"")

 

[BadFish523]

Ok, so I got it to return the information. I was using the wildcard "*no*" because there is a little other information in that cell but apparently something about that was throwing it off. If I put the whole text "no (if no must fill in comment below)" and it is now returning the correct information but I'm still hoping to ignore those that don't meet the criteria if anybody knows of a way to do that? Was hoping for something that would search through 'Data1'!H2:H="Yes,'Data1'!I2:I="No (if no must fill in comment)",'Data1'!D2:D kind of something like that so it will only list the ones that meet the criteria and ignore the others?

 

[Aladin Akyurek]

It seems you want a sublist which meets the criteria...

=ARRAYFORMULA(IFERROR(INDEX('Data1'$D$2:$D,SMALL(IF('Data1'!$H$2:$H="Yes",IF('Data1'!$I$2:$I="No",ROW('Data1'$D$2:$D)-ROW(INDEX('Data1'$D$2:$D,1,1))+1)),ROWS($1:1))),""))

 

[BadFish523]

It seems you want a sublist which meets the criteria...

=ARRAYFORMULA(IFERROR(INDEX('Data1'$D$2:$D,SMALL(IF('Data1'!$H$2:$H="Yes",IF('Data1'!$I$2:$I="No",ROW('Data1'$D$2:$D)-ROW(INDEX('Data1'$D$2:$D,1,1))+1)),ROWS($1:1))),""))

This works, I just wish I didnt have to drag it down to continue the list but it should do the trick. Thank you very much! As always great and helpful people on the forum here!

 

[Aladin Akyurek]

This works, I just wish I didnt have to drag it down to continue the list but it should do the trick. Thank you very much! As always great and helpful people on the forum here!

You are welcome. Thanks for the update.