Forum Discussion
IF Formula trouble
do you have to do this with formula? the easiest way is to use a pivot table. filter the data and get required data with getpivotdata function. or you can use Pivot Table as it is.
I have never used a pivot table but if it can get me what I want that is fine. I have made a very simple version of sheet. Column a is name, b is info a and c is info b. In column F, I would like a list of names that have 1 for both info a and info b. My original formula listed the names but with only one condition, info a.
Thank you.
Here is the pivot table version. You can create a table for your main table if you wish. Which will allow a dynamic range which means the range will expand whenever you add some data. and then refresh pivot table to reflect the changes. hope this helps.
I also add another way to do this with formula. After writing the formula you have to press SHIFT+CTRL+ENTER. The formula is a bit complex. it is an array formula and can find what you need with multiple criterias.
Just for the collection, non-array formulas could be
=IFNA(INDEX($B$4:$B$11,MATCH(1,INDEX(NOT(COUNTIF($G$3:$G3,$B$4:$B$11))*($C$4:$C$11=$G$2)*($D$4:$D$11=$H$2),0,),0),),"")
or
=IFNA(LOOKUP(2,1/NOT(COUNTIF($H$3:$H3,$B$4:$B$11))/($C$4:$C$11=$G$2)/($D$4:$D$11=$H$2),$B$4:$B$11),"")
or
=IFERROR(INDEX(B:B,AGGREGATE(15,6,1/($C$4:$C$11=$G$2)/($D$4:$D$11=$H$2)*ROW($B$4:$B$11),ROW()-ROW($I$3))),"")
