• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need help..index and match..

khyriel

New Member
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,
 
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
 
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.
 
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
 
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.
 
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
 
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))))
 
Back
Top