DATA
DATE****NAME****TRANSPORT
42388****CHEN****TCIXPS
42388****SURA****TCIXPS
42388****GURG****V-XPRESS
42388****GOA****TCIXPS
42388****RAIP****TCIXPS
42388****CHEN****TCIXPS
42388****DELH****TCIXPS
42388****AHME****TCIXPS
42388****FARI****TCIXPS
42388****PUNE****V-XPRESS
42388****DELH****TCIXPS
42388****INDO****V-XPRESS
42388****NAGP****V-XPRESS
42388****RANC****GATI
42388****JABA****TCIXPS
REQUIRED FORMAT:
DTDC GATI*****TCIXPS***V-XPRESS
RANC**** CHEN**** GURG
SURA**** PUNE
GOA ****INDO
RAIP ****NAGP
CHEN
DELH
AHME
FARI
DELH
JABA
In A3{=IFERROR(INDEX(DATA!$C:$C,SMALL(IF((DATA!$D$2:$D$20=B$2)*(DATA!$B$2:$B$20=$D$1),ROW(DATA!$C$2:$C$20)),ROWS(B$2:B2))),"")}
The above said formula working perfectly when index range $C:$C.
Try to edit index range $C$2:$C$20 Value return is wrongly.
In A3{=IFERROR(INDEX(DATA!$C$2:$C$45,SMALL(IF((DATA!$D$2:$D$20=A$2)*(DATA!$B$2:$B$20=$D$1),ROW(DATA!$C$2:$C$20)),ROWS(A$2:A2))),"")}
Please give me your suggestion.
Herewith attached file.
Bookmarks