Index Match multiple sheets

[sureshsonti]

Hello,
I am trying to use Index/Match function to move data to different sheets based on departments. My boss wants to create a master list and split this list based on departments. Trying to create this dynamically.

I have attached sample data.

Thanks for your help

 

[bosco_yip]

1] In "salesdata" sheet B3, copied down :

=IFERROR(INDEX(master[Vendor],AGGREGATE(15,6,ROW(master[Vendor])-ROW(master[[#Headers],[Vendor]])/(master[Department]=$A$1),ROWS($1:1))),"")

2] In "salesdata" sheet C3, copied down :

=IF(B3="","",INDEX(master[Phone],MATCH($B3,master[Vendor],0)))

3] In "salesdata" sheet D3, copied down :

=IF(B3="","",INDEX(master[Address],MATCH($B3,master[Vendor],0)))

4] In "salesdata" sheet A3, copied down :

=IF(B3="","",$A$1)

Then,

5] Select "salesdata" sheet A3:D10>> copy/paste to the other sheets

Regards
Bosco

 

[sureshsonti]

Mr. Bosco,
Really appreciate your help on this issue. I have a question on the AGGREGATE function. How did you know that you have to use AGGREGATE function in the formula. I would like to learn more about the INDEX / MATCH functions. Where can I learn more about this function.

Thanks for your help again.