Index & match formula

[helponmacro]

Hi All - I have a file that records all staff holiday. As most of the staff made multiple application, I need a formula that help to combine their request into one row (unique by staff name)in gannt chart format. I've attached the file here.
I tried to use index and match but it doesn't work. Can someone help me to take look and advise? Thanks in advance

[Mazzaropi]

helponmacro, Good morning.

Try to use:

E15 --> =IFERROR(INDEX(E3:E8,SUMPRODUCT(($B$3:$B$8=$B$15)*(E3:E8<>0)*ROW(E3:E8))-2),0)

I applied formula at your example.

Is that what you want?

I hope it helps.

[Glenn Kennedy]

Mazzaropi's solution works for Mickey. But to make it generic for everyone...

=IFERROR(INDEX(E$3:E$8,SUMPRODUCT(($B$3:$B$8=$B13)*(E$3:E$8<>0)*ROW(E$3:E$8))-2),0)

in E13, copied across and down.

[helponmacro]

Thanks so much Mazzaropi and Glenn

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Mazzaropi]

Glenn Kennedy, Good morning.

Very well observed!

Have a nice weekend.

[Mazzaropi]

Thanks so much Mazzaropi and Glenn

helponmacro, Good morning.

Thanks for the feedback.

Have a nice day!