Microsoft Excel

Microsoft Excel (Bài 85): Hàm tìm kiếm VLOOKUP và VLOOKUP nâng cao nhiều cột, nhiều điều kiện

Posted on by HKT

Nếu công việc của bạn thường xuyên làm việc với Excel và xử lý dữ liệu thì việc phải nắm vững các hàm tìm kiếm là bắt buộc. Trong bài viết này, mình sẽ chia sẻ với các bạn hai hàm tìm kiếm thường xuyên được sử dụng là hàm Vlookup và Hlookup.

Hàm VLOOKUP

VLOOKUP là hàm dùng để tìm kiếm giá trị tương ứng trên ô cột đầu tiên của bảng tham chiếu và cho kết quả tương ứng trong cột chỉ định

Cú Pháp:

VLOOKUP(Lookup_value,Table_ array,Col_index_Num,Range_lookup)

Trong đó:

  • Lookup_value: giá trị đối chiếu với cột đầu tiên của bảng tham chiếu để lấy được giá trị cần tìm.
  • Table_ array: địa chỉ tuyệt đối của bảng tham chiếu, không lấy dòng tiêu đề.
  • Col_index_Num: Số thứ tự cột chứa giá trị cần lấy của bảng tham chiếu (tính theo thứ tự từ trái qua phải và bắt đầu từ 1).
  • Range_lookup: Cách tìm kiếm trên bảng tham chiếu. + 0: Cột đầu tiên của bảng tham chiếu chưa sắp xếp. + 1: Cột đầu tiên của bảng tham chiếu đã sắp xếp theo chiều tăng dần (ngầm định).

Mình có ví dụ như sau cho các bạn dễ hiểu nhé

Để tìm kiếm giá trị mức thưởng của bảng tham chiếu và cho ra kết quả Học Bổng tương ứng với mức thưởng đó chúng ta làm như sau:

Ta có: =VLOOKUP(K6;$A$12:$B$14;2;0) các bạn chú ý vì mình sử dụng Excel 2013 nên khoảng cách giữa các đối số là dấu ; còn với Excel 2007,2010 là dấu (,) nhé.

Trong đó:

  • K6: Cột Mức thưởng cần đối chiếu với cột đầu tiên của bảng tham chiếu
  • $A$12:$B$14 : là địa chỉ tuyệt đối của bảng tham chiếu
  • 2: cột học bổng là cột thứ 2 của bảng tham chiếu
  • 0: cột đầu tiên của bảng tham chiếu chưa sắp xếp

Kết quả nhận được:

Hàm tìm kiếm VLOOKUP nâng cao nhiều cột, nhiều điều kiện

Hãy bắt đầu bằng ví dụ sau đây (Các bạn có thể tìm thấy VD này trong Sheet1 ở bảng tính Excel phía trên)

Ở đây, để tra cứu được giá trị ở Cột C mà điều kiện là Bb thì chúng ta cần thêm một Cột phụ như ở C9:C15. Nhưng khi chúng ta không được phép thay đổi cấu trúc của bảng tính, thì chúng ta không thể sử dụng cách này. Làm thế nào để có thể không thay đổi cấu trúc của bảng tính mà vẫn có thể thực hiện được việc này? Chúng ta có thể chuyển sang Sheet2.

SỬ DỤNG VLOOKUP CÙNG VỚI CHOOSE VÀ CÔNG THỨC MẢNG ĐỂ TRA CỨU DỮ LIỆU DỰA TRÊN NHIỀU CỘT:

Trong Sheet2, chúng ta sử dụng công thức mảng sau đây tại ô F2 để có thể tra cứu được dữ liệu. Để nhập công thức này, các bạn nhớ dùng tổ hợp phím CTRL + SHIFT + ENTER

=VLOOKUP(E2,CHOOSE({1,2},$A$2:$A$7&$B$2:$B$7,$C$2:$C$7),2,0)

Ở trong công thức, các phần được tô màu là phần các bạn có thể thay đổi tuỳ theo nhu cầu sử dụng. Để hiểu được công thức, chúng ta có thể theo dõi ảnh sau cùng với từng bước của hàm CHOOSE.

Ở đây, hàm CHOOSE đã tạo cho chúng ta 1 bảng dữ liệu gồm 2 cột ngay trong công thức để chúng ta có thể sử dụng được hàm VLOOKUP một cách hoàn toàn bình thường. Cách dùng hàm CHOOSE, các bạn có thể xem thêm ở ảnh sau đây hoặc ở trong bảng tính Excel kèm theo bài học.

Hàm CHOOSE sử dụng trong bài này của chúng ta là hàm mảng nên nếu bạn muốn thử kết quả của hàm CHOOSE trên bảng tính Excel thì các bạn có thể sử dụng công thức sau ở trong thanh công thức trên Excel ở máy tính của bạn, nhớ đánh dấu công thức trên thanh công thức và nhấn phím F9, nếu bạn dùng laptop, có thể bạn phải dùng tổ hợp phím FN + F9:

=CHOOSE({1,2},"Học","Excel","Online",'Miễn","Phí")

Kết quả bạn nhận được ở ngay trên thanh công thức là ={"Học","Excel"}

Nếu chưa hiểu, các bạn có thể xem thêm videos sau đây:

THÊM 2 VÍ DỤ VỚI VLOOKUP VÀ CHOOSE:

TRA CỨU DỮ LIỆU DỰA TRÊN ĐIỀU KIỆN TỪ 3 CỘT

Ví dụ này được lưu trong Sheet3 của bảng tính ở phía trên. Trong ví dụ này, chúng ta có thể dùng kĩ thuật vừa được miêu tả để tra cứu dữ liệu từ cột D dựa trên dữ liệu ở cột A, cột B và cột C. Đến đây, hi vọng các bạn có thể xử lý thành thạo dù dữ liệu có đến từ bao nhiêu cột đi nữa.

TRA CỨU DỮ LIỆU ĐẶC BIỆT

Ví dụ này có thể được tìm thấy trong Sheet4 của bảng tính phía trên. Chúng ta có 2 cột Họ và Tên, dựa trên 2 cột này chúng ta muốn tìm ra số báo danh của một người. Ở ví dụ này, cách nối Họ và Tên được sử dụng để tạo nên tên đầy đủ, có thể nối Họ và Tên bởi dấu cách, dấu nối, hoặc dấu phẩy cộng thêm dấu cách, các công thức được sử dụng cho từng trường hợp đó là (lưu ý, công thức mảng, nhấn CTRL + SHIFT + ENTERđể nhập):

  • =VLOOKUP(A7,CHOOSE({1,2},$A$2:$A$4&" "&$B$2:$B$4,$C$2:$C$4),2,0)
  • =VLOOKUP(A8,CHOOSE({1,2},$A$2:$A$4&"-"&$B$2:$B$4,$C$2:$C$4),2,0)
  • =VLOOKUP(A9,CHOOSE({1,2},$A$2:$A$4&", "&$B$2:$B$4,$C$2:$C$4),2,0)