Can I get multiple values returned for an IF formula?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a simple table with student names in B2:L2 and words in A2:A221.
Under each student name next to the word, I put a 1 if they knew the word or
a 0 if they did not.
Question: Can I have the students name returned if they had a 0 in the box
for a particular word? Example: In A2 the word is 'the'. Under student A I
entered a 1, under student B a 0, and student C a 0, etc. I want a formula
that will return each name that has a 0 for that word.
 
Hi
Maybe the easiest way would be to mark your range B2:L2
Data>Filter>Autofilter.
Use the dropdown to select either 0 or 1 as required.

Regards

Roger Govier
 
Another option to try ..

Sample construct at:
http://www.savefile.com/files/2931036
Returning_MultipleColValues_TeachCTC_newusers.xls

Assume source table is in Sheet1,
student names in B2:L2, words in A3:A221
(think there was a typo in the words range,
should start in A3, not A2)

Using empty cols to the right,
Put in N3: =IF(B3="","",IF(B3=0,COLUMN(),""))
Copy N3 across to X3, fill down to X221

In a new Sheet2
-------------
Put in A2, copy down to A220:
=IF(Sheet1!A3="","",Sheet1!A3)

Put in B2:
=IF(ISERROR(SMALL(Sheet1!$N3:$X3,COLUMNS($A$1:A1))),"",
INDEX(Sheet1!$B$2:$L$2,
MATCH(SMALL(Sheet1!$N3:$X3,COLUMNS($A$1:A1)),Sheet1!$N3:$X3,0)))

Copy B2 across to L2, fill down to L220

Sheet2 will return the student names,
neatly bunched at the left next to col with the words (col A)
 
Back
Top