Can I get multiple values returned for an IF formula?

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


TeachCTC wrote:
> 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.

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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"TeachCTC" <[email protected]> wrote in message
news:[email protected]...
> 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.