Chuyển tới nội dung chính

Bài 7: Nhóm Hàm Tìm Kiếm & Tham Chiếu Dữ Liệu

Giai đoạn 2 – Trung cấp · Mục tiêu: Thành thạo VLOOKUP, HLOOKUP, INDEX & MATCH và giới thiệu XLOOKUP – bộ hàm quyền lực nhất trong Excel.

1. Hàm VLOOKUP – Tìm kiếm theo cột dọc​

Cú pháp​

=VLOOKUP(giá_trị_tìm, bảng_tham_chiếu, cột_trả_về, kiểu_tìm)
Tham sốÝ nghĩa
giá_trị_tìmGiá trị cần tìm (thường là mã, tên)
bảng_tham_chiếuVùng dữ liệu chứa bảng tra cứu
cột_trả_vềSố thứ tự cột muốn lấy kết quả (đếm từ 1)
kiểu_tìm0 = chính xác, 1 = tương đối (gần đúng)

Ví dụ​

Bảng tra cứu giá sản phẩm tại Sheet2!A:C:

A (Mã SP)B (Tên SP)C (Đơn giá)
SP001Bút bi5,000
SP002Vở8,000
SP003Thước3,000
=VLOOKUP("SP002", Sheet2!A:C, 3, 0)
→ Kết quả: 8,000 (đơn giá của Vở)

:::warning Hạn chế của VLOOKUP

  1. Chỉ tìm kiếm từ trái qua phải – cột tìm kiếm phải là cột đầu tiên của bảng.
  2. Nếu thêm/xóa cột, số thứ tự cột sẽ bị sai → phải sửa thủ công.
  3. Chậm khi dữ liệu lớn (hàng trăm nghìn dòng). :::

2. Hàm HLOOKUP – Tìm kiếm theo dòng ngang​

Cú pháp​

=HLOOKUP(giá_trị_tìm, bảng_tham_chiếu, dòng_trả_về, kiểu_tìm)

Tương tự VLOOKUP nhưng bảng dữ liệu được tổ chức theo hàng ngang thay vì dọc.

Ví dụ​

ABCD
1Tháng 1Tháng 2Tháng 3Tháng 4
2100150200180
380120160140
=HLOOKUP("Tháng 3", A1:D3, 2, 0)
→ Kết quả: 200 (giá trị dòng 2 của cột "Tháng 3")

3. Bộ đôi INDEX & MATCH – Giải pháp linh hoạt​

Tại sao cần INDEX & MATCH?​

  • Khắc phục mọi hạn chế của VLOOKUP.
  • Có thể tìm kiếm từ phải qua trái (VLOOKUP không làm được).
  • Không phụ thuộc vào số thứ tự cột → an toàn hơn khi chèn/xóa cột.

Hàm MATCH – Tìm vị trí​

=MATCH(giá_trị_tìm, vùng_tìm, kiểu_tìm)

→ Trả về số thứ tự (vị trí) của giá trị trong vùng.

=MATCH("SP002", A2:A100, 0)    → Kết quả: 2 (vị trí thứ 2)

Hàm INDEX – Lấy giá trị theo vị trí​

=INDEX(vùng_dữ_liệu, dòng, [cột])
=INDEX(C2:C100, 2)    → Lấy giá trị ở vị trí thứ 2 trong cột C

Kết hợp INDEX & MATCH​

=INDEX(cột_kết_quả, MATCH(giá_trị_tìm, cột_tìm_kiếm, 0))

Ví dụ: Tìm đơn giá từ mã sản phẩm (tương đương VLOOKUP nhưng linh hoạt hơn):

=INDEX(C2:C100, MATCH("SP002", A2:A100, 0))
→ Kết quả: 8,000

Ví dụ: Tìm ngược từ phải qua trái – Biết đơn giá, tìm tên sản phẩm:

=INDEX(B2:B100, MATCH(8000, C2:C100, 0))
→ Kết quả: "Vở"

:::tip So sánh nhanh

Tiêu chíVLOOKUPINDEX & MATCH
Hướng tìm kiếmTrái → PhảiBất kỳ hướng nào
Phụ thuộc cộtCó (cột số)Không
Tốc độChậm hơnNhanh hơn
Độ linh hoạtThấpCao
:::

4. Hàm XLOOKUP – Thế hệ mới (Excel 365 / 2021+)​

Cú pháp​

=XLOOKUP(giá_trị_tìm, vùng_tìm, vùng_kết_quả, [giá_trị_mặc_định], [kiểu_khớp], [chế_độ_tìm])

Ưu điểm so với VLOOKUP​

  • Tìm kiếm mọi hướng (dọc/ngang, trái/phải).
  • Tham số giá_trị_mặc_định tích hợp sẵn (không cần IFERROR).
  • Cú pháp đơn giản, dễ đọc hơn.

Ví dụ​

=XLOOKUP("SP002", A2:A100, C2:C100, "Không tìm thấy")
→ Kết quả: 8,000

=XLOOKUP("SP999", A2:A100, C2:C100, "Không tìm thấy")
→ Kết quả: "Không tìm thấy"

:::info Tương thích XLOOKUP chỉ có trên Excel 365 và Excel 2021 trở lên. Nếu dùng phiên bản cũ hơn, hãy sử dụng INDEX & MATCH. :::

Bài tập thực hành​

Sheet1 – Đơn hàng: Mã SP, Số lượng
Sheet2 – Bảng giá: Mã SP, Tên SP, Đơn giá, Nhóm hàng

  1. Dùng VLOOKUP để điền Tên SP và Đơn giá vào Sheet1.
  2. Dùng INDEX & MATCH để điền Nhóm hàng (cột nằm bên trái cột Mã SP).
  3. Bọc tất cả công thức trong IFERROR để xử lý mã SP không tồn tại.
  4. (Nếu có Excel 365) Thử lại bằng XLOOKUP.

Bài trước: Bài 6 – Hàm Thống Kê Điều Kiện · Bài tiếp: Bài 8 – Hàm Chuỗi & Thời Gian