Bạn hãy thử dùng hàm sau:
Cột B:Mã BP
=IF(ISERROR(IF(ISERROR(IF(ISERROR(VLOOKUP($A4,BGD,2,)),VLOOKUP($A4,PKH,2,),VLOOKUP($A4,BGD,2,))),VLOOKUP($A4,PTC,2,),IF(ISERROR(VLOOKUP($A4,BGD,2,)),VLOOKUP($A4,PKH,2,),VLOOKUP($A4,BGD,2,)))),VLOOKUP($A4,PVT,2,),IF(ISERROR(IF(ISERROR(VLOOKUP($A4,BGD,2,)),VLOOKUP($A4,PKH,2,),VLOOKUP($A4,BGD,2,))),VLOOKUP($A4,PTC,2,),IF(ISERROR(VLOOKUP($A4,BGD,2,)),VLOOKUP($A4,PKH,2,),VLOOKUP($A4,BGD,2,))))
Cột C: Họ Tên
=IF(ISERROR(IF(ISERROR(IF(ISERROR(VLOOKUP($A4,BGD,3,)),VLOOKUP($A4,PKH,3,),VLOOKUP($A4,BGD,3,))),VLOOKUP($A4,PTC,3,),IF(ISERROR(VLOOKUP($A4,BGD,3,)),VLOOKUP($A4,PKH,3,),VLOOKUP($A4,BGD,3,)))),VLOOKUP($A4,PVT,3,),IF(ISERROR(IF(ISERROR(VLOOKUP($A4,BGD,3,)),VLOOKUP($A4,PKH,3,),VLOOKUP($A4,BGD,3,))),VLOOKUP($A4,PTC,3,),IF(ISERROR(VLOOKUP($A4,BGD,3,)),VLOOKUP($A4,PKH,3,),VLOOKUP($A4,BGD,3,))))
Cột D: Lương
=IF(ISERROR(IF(ISERROR(IF(ISERROR(VLOOKUP($A4,BGD,4,)),VLOOKUP($A4,PKH,4,),VLOOKUP($A4,BGD,4,))),VLOOKUP($A4,PTC,4,),IF(ISERROR(VLOOKUP($A4,BGD,4,)),VLOOKUP($A4,PKH,4,),VLOOKUP($A4,BGD,4,)))),VLOOKUP($A4,PVT,4,),IF(ISERROR(IF(ISERROR(VLOOKUP($A4,BGD,4,)),VLOOKUP($A4,PKH,4,),VLOOKUP($A4,BGD,4,))),VLOOKUP($A4,PTC,4,),IF(ISERROR(VLOOKUP($A4,BGD,4,)),VLOOKUP($A4,PKH,4,),VLOOKUP($A4,BGD,4,))))
Không biết phải vậy không, bạn thử xem sao nhé!