need help..index and match..

[khyriel]

1-Feb PUBLIC HOLIDAY

10-Feb SATURDAY

15-Feb SUNDAY

20-Feb SATURDAY

21-Feb SATURDAY

22-Feb SUNDAY

23-Feb SATURDAY

24-Feb SUNDAY

25-Feb 7:30 - 4:30 PM

26-Feb SUNDAY

27-Feb SATURDAY


From these data, i use this formula '=INDEX(A1:A11,MATCH("SATURDAY",B1:B11,0))


and the result shown as below,only returns the first data match.i want the date place side by side after the value match.thanks for any help.


Saturday: 10/02,

Sunday: 15/02,

Public Holiday: 01/02,

 

[Hui]

Khyriel

If you have

G12: Saturday

G13: Sunday

G14: PUBLIC HOLIDAY


then in

H12: =INDEX($A$1:$A$11,MATCH(G12,$B$1:$B$11,0))

Copy H12 Down

 

[xld]

Try using this array formula


=IF(ISERROR(SMALL(IF($B$1:$B$20="SATURDAY",ROW($B$1:$B$20)),COLUMN(A$1))),"",

INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20="SATURDAY",ROW($B$1:$B$20)),COLUMN(A$1))))


copy across as many as might be needed.


This is an array formula.

This means that you use Ctrl-Shift-Enter to commit the formula, not just Enter (array Enter it). Excel will put curly brackets around the formula in the formula bar, you don#t do this.

If you need to change the formula at any time, you must array Enter it again.

 

[khyriel]

i've tried it both formula from Hui and xld, thanks alot.

the result only appears 1 result.

Saturday 10/2

Sunday 15/2

Public Holiday 01/2


can it show all dates in the same cell like example:


Saturday 10/2,20/2,21/2,23/2,27/2

Sunday 15/2,22/2,24/2,26/2

Public Holiday 01/2


thanks for all help

 

[xld]

My example shows all results in adjacent cells if you drag the formula across. I would suggest that this is the best way to do it.

 

[khyriel]

A B C D E F G H

1-Feb PUBLIC HOLIDAY SATURDAY 10/02 20/02 21/02 23/02 27/02

10-Feb SATURDAY SUNDAY 15/02 22/02 24/02 26/02 #NUM!

15-Feb SUNDAY PUBLIC HOLIDAY 01/02 #NUM!<--- Can sumone xplaine this?

20-Feb SATURDAY

21-Feb SATURDAY

22-Feb SUNDAY

23-Feb SATURDAY

24-Feb SUNDAY

25-Feb 7:30 - 4:30 PM

26-Feb SUNDAY

27-Feb SATURDAY

 

[xld]

I don't get a #NUM! in either case, I get blank both times.

 

[khyriel]

i didnt know wheres the problem..this is the formula in the cell..


=IF(ISERROR(SMALL(IF(B1:B11=C3,ROW(B1:B11)),COLUMN(A1:A11))),"",INDEX(A1:A11,SMALL(IF(B1:B11=C3,ROW(B1:B11)),COLUMN(B1))))