IF/then statement help.....

H

HxR

I am trying to write a If statement but I do not know how to make it
answer the way I want it to.
I have two worksheets.
Sheet1 is where the answer will be. Sheet2 is where I am getting my
information.

Sheet2:
Column:
A-----------B
Show1 Sunday
Show2 Monday
Show3 Sunday
Show4 Monday
Show5 Friday
Show6 Tuesday

I want to say if col B = Monday then show what is next to the monday
ie. Show2 and Show4. I want to be able to run this on more than one
cell showing all results in seperate cells.

This is something I do not think can be done but I am wondering if
maybe someone might have an idea.
Thank you
 
B

Bob Phillips

Assuming that the test value is in A1, a group of cells to contain the
results (max possible), and in the formula bar, enter

=IF(ISERROR(SMALL(IF(Sheet2!$B$1:$B$20=A1,ROW($A1:$A20),""),ROW($A1:$A20))),
"",
INDEX(Sheet2!$A$1:$A$20,SMALL(IF(Sheet2!$B$1:$B$20=A1,ROW($A1:$A20),""),ROW(
$A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

Martin P

With your sample information in Sheet 2, cells A1 to B6, enter the following:
Cell C1: =SUMPRODUCT(--(B1=$B$1:B1))
Cell D1: =ROW()
Copy C1:D1 to C1:D6
Enter Sunday to Saturday in F2 to F6
G1: =COLUMN(F1)-COLUMN($F$1)+1
Copy G1 to G1:J1
G2: =SUMPRODUCT($D$1:$D$6,--($B$1:$B$6=$F2),--($C$1:$C$6=G$1))
Copy G2 to G2:J8
Now in Sheet 1:
In A2:A8 enter Sunday to Saturday
B2: =IF(Sheet2!G2<>0,INDEX(Sheet2!$A$1:$A$6,Sheet2!G2),"")
Copy B2 to B2:E8
 
Top