Hàm VLOOKUP trong Excel: Nên dùng VLOOKUP, HLOOKUP hay INDEX & MATCH?

Excel cung cấp một số hàm tìm kiếm và tham chiếu để sử dụng dữ liệu trong trang tính hiện tại và tìm kiếm các giá trị dữ liệu tương tự trong trang tính khác. Hàm VLOOKUP trong Excel cùng với HLOOKUP hay INDEX và MATCH là hai hàm tham chiếu và tra cứu được ứng dụng rất nhiều. Dưới đây là cú pháp hàm cùng với ví dụ minh hoạ, Tinhocmos đưa ra để giúp bạn hiểu rõ hơn.

Hàm VLOOKUP trong Excel – Hàm tham chiếu cột

Công dụng: Trả về giá trị được tìm kiếm trong một bảng hoặc một dải ô theo cột

Cú pháp: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Trong đó: 

• Lookup_value: giá trị tham chiếu, giá trị này phải thuộc cột đầu tiên của table_array
• Table_array: bảng giá trị tham chiếu, bảng này phải để ở địa chỉ tuyệt đối (sử dụng $ phía trước)
• Col_index_num: thứ tự của cột cần lấy dữ liệu trong table_array, thứ tự này tính từ 1 cho cột ngoài cùng bên trái table_array
• Range_lookup (tuỳ chọn): giá trị chỉ định Vlookup tìm giá trị tuyệt đối hay giá trị tương đối. Thường mặc định là TRUE (tương đương với 1: giá trị tương đối, gần đúng). Nếu muốn tìm giá trị tuyệt đối sử dụng FALSE (tương đương với 0)

Ví dụ: Bạn có mã của sản phẩm, và muốn tìm giá của sản phẩm có mã này theo chiều dọc

Nhập công thức vào ô C14 là =VLOOKUP(B14,Table1[#All], 5,0), trong đó

• B14 là mã sản phẩm mà bạn đang muốn tìm kiếm giá
• Table1[#All] là vùng dữ liệu tham chiếu chứa mã sản phẩm và giá tương ứng
• 5 là số cột chứa giá của sản phẩm
• 0 là lấy giá trị tuyệt đối

 

Xem thêm: Nhóm hàm random trong Excel (hàm RAND và RANDBETWEEN)

Hàm HLOOKUP – Hàm tham chiếu hàng

Công dụng: Trả về giá trị được tìm kiếm trong một bảng hoặc một dải ô theo hàng. Hàm này về bản chất giống với hàm VLOOKUP, chỉ khác ở chỗ là tìm kiếm theo hàng, còn VLOOKUP là tìm kiếm theo cột

Cú pháp: = HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Trong đó: 

• Lookup_value: giá trị tham chiếu, giá trị này phải thuộc hàng đầu tiên của table_array
• Table_array: bảng giá trị tham chiếu, bảng này phải để ở địa chỉ tuyệt đối ( sử dụng $ phía trước)
• Row_index_num: thứ tự của hàng cần lấy dữ liệu trong table_array, thứ tự này tính từ 1 cho hàng thứ nhất trong table_array
• Range_lookup (tuỳ chọn): giá trị chỉ định Hlookup tìm giá trị tuyệt đối hay giá trị tương đối. thường mặc định là TRUE (tương đương với 1: giá trị tương đối, gần đúng), muốn tìm giá trị tuyệt đối sử dụng FALSE (tương đương với 0).

 Ví dụ: Bạn có mã của sản phẩm, và muốn tìm giá của sản phẩm có mã này theo chiều ngang

Nhập công thức vào ô C10 là =HLOOKUP(B10,A1:F5,5,0), trong đó

• B10 là mã sản phẩm mà bạn đang muốn tìm kiếm giá
• A1:F5 là vùng dữ liệu tham chiếu chứa mã sản phẩm và giá tương ứng
• 5 là số dòng chứa giá của sản phẩm
• 0 là lấy giá trị tuyệt đối

Xem thêm: Tính tuổi trong Excel nhanh chóng với hàm YEAR và DATEDIF

Hàm INDEX & MATCH – Kết hợp hai hàm tham chiếu

Hàm INDEX và hàm MATCH là sự kết hợp hoàn hảo để khắc phục hạn chế của VLOOKUP và HLOOKUP.

Công dụng:

• Hàm MACTH cho ra số hàng số cột của dữ liệu trong bảng.
• Hàm INDEX xuất giá trị ô dữ liệu trong bảng từ số hàng và số cột.

Cú pháp: =INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])
Trong đó:

• Array: Một phạm vi ô hoặc một hằng số mảng. Nếu mảng chỉ chứa một hàng hoặc cột, tham đối số giá row_num hoặc column_num tương ứng là tùy chọn.
• row_num: Chọn hàng trong mảng mà từ đó trả về một giá trị.
• column_num: Chọn cột trong mảng mà từ đó trả về một giá trị.
• lookup_value: Giá trị mà bạn muốn so khớp trong mảng tìm kiếm.
• match_type: Kiểu khớp: Số -1, 0 hoặc 1. Đối số kiểu khớp chỉ rõ cách Excel so khớp giá trị tìm kiếm với các giá trị trong mảng tìm kiếm. Giá trị mặc định cho đối số này là 1

 Ví dụ: Bạn muốn tìm kiếm giá của sản phẩm cho trước tại ô C13

Nhập công thức vào ô C14 là =INDEX(A1:E10,MATCH(C13,B1:B10,0),MATCH(B14,A1:E1,0)), trong đó

• A1:E10 mảng tham chiếu chứa tên và giá của sản phẩm
• MATCH(C13,B1:B10,0) là hàm trả về số hàng trong mảng tương ứng với tên sản phẩm cần tìm giá
  • C13 là ô chứa tên sản phẩm
  • B1:B10 là cột chứa tên của tất cả các sản phẩm trong vùng tham chiếu
  • 0 là trả về giá trị tuyệt đối
• MATCH(B14,A1:E1,0) là hàm trả về số cột trong mảng tương ứng với giá của sản phẩm
  • B14 là tên cột trong mảng
  • A1:E1 là dòng tiêu đề
  • 0 là trả về giá trị tuyệt đối

Xem thêm: Nhóm hàm Averageif trong Excel chắc chắn dân kế toán phải biết

Tóm lại về nhóm hàm tham chiếu trong Excel

Tìm kiếm và tham chiếu là một trong top những chức năng rất hữu ích mà Excel mang lại cho người dùng. Các hàm tìm kiếm và tham chiếu sử dụng dữ liệu trong trang tính hiện tại và tìm kiếm các giá trị dữ liệu tương tự trong cả trang tính khác. Thành thạo tính năng này sẽ là nền tảng vững chắc để bạn tiếp cận với khối kiến thức nâng cao hơn.

Nếu bạn muốn thực hành thêm thì hãy tải file thực hành tại đây: Hàm VLOOKUP, HLOOKUP, INDEX VÀ MATCH

Bạn nên dùng hàm HLOOKUP hoặc hàm VLOOKUP trong Excel trong trường hợp không cần thêm cột dữ liệu trong bảng. Còn nếu mà bạn sẽ chỉnh sửa thêm dữ liệu thì hãy sẽ dụng hàm INDEX kết hợp với hàm MATCH vì sự nhanh chóng và tự động cập nhật khi số dòng hoặc cột trong bảng thay đổi

Ngoài ra, nếu công việc của bạn gắn liền với Tin học văn phòng nhưng vì quá bận rộn nên bạn không có thời gian tham gia các lớp học trực tiếp ở trung tâm thì bạn có thể tham khảo khóa Luyện thi MOS của Trung tâm tin học văn phòng MOS nhé!

Thông tin liên hệ 

Trung tâm tin học văn phòng MOS – Viện Đào tạo và Tư vấn Doanh nghiệp – Đại học Ngoại Thương

Email: [email protected]

Hotline: 0914 444 343