Index Match trong Excel: Cách tra cứu dữ liệu hiệu quả hơn

INDEX MATCH, sự kết hợp của hai hàm Excel INDEX() và MATCH(), là một phương án thay thế cho hàm VLOOKUP() thường dùng.

Trong hướng dẫn này, tôi sẽ giới thiệu cách INDEX() và MATCH() hoạt động riêng lẻ và cùng nhau, cách thực hiện tra cứu dọc và ngang, cùng các thực hành tốt nhất khi dùng INDEX MATCH với ví dụ thực tế.

INDEX MATCH trong Excel là gì?

INDEX MATCH là cách viết tắt để chỉ việc lồng hai hàm. Nó tương đương với ý tưởng INDEX(MATCH()). Hãy xem mỗi hàm hoạt động thế nào riêng lẻ và khi kết hợp.

Hàm INDEX() trong Excel

Hàm INDEX() trong Excel trả về một giá trị hoặc tham chiếu đến giá trị trong một phạm vi hoặc bảng. Bạn có thể dùng theo hai cách: dạng mảng và dạng tham chiếu.

Dạng mảng

Bạn có thể dùng dạng mảng khi làm việc với một bảng hoặc mảng cố định. Ở dạng này, INDEX() lấy ra một giá trị dựa trên số hàng và số cột bạn chỉ định. Cú pháp là:

INDEX(array, row_num, [column_num])Được hỗ trợ bởi

Giải thích các đối số:

• array (bắt buộc) là phạm vi hoặc mảng giá trị bạn đang làm việc.

• row_num (bắt buộc) là số hàng cần trả về giá trị.

• column_num (tùy chọn) là số cột cần trả về giá trị.

Bạn phải chỉ định ít nhất một trong hai, row_num hoặc column_num, để Excel xác định vị trí giá trị.

Một vài lưu ý nhanh khi dùng cú pháp này:

• Nếu mảng chỉ có một hàng hoặc một cột, bạn có thể bỏ qua row_num hoặc column_num.

• Nếu cả row_num và column_num đều có, INDEX trả về giá trị tại điểm giao nhau của chúng.

• Nếu đặt row_num hoặc column_num bằng 0, Excel trả về toàn bộ cột hoặc hàng (bạn cần nhập dạng công thức mảng để thấy kết quả).

Dạng tham chiếu

Bạn có thể dùng dạng tham chiếu khi làm việc với nhiều phạm vi và cần chỉ định phạm vi nào sẽ tra cứu.

Cú pháp là:

INDEX(reference, row_num, [column_num], [area_num])Được hỗ trợ bởi

Giải thích các đối số:

• reference (bắt buộc) dùng để tham chiếu một hoặc nhiều phạm vi ô.

• row_num (bắt buộc) là số hàng trong vùng đã chọn.

• column_num (tùy chọn) là số cột trong vùng đã chọn.

• area_num (tùy chọn) cho Excel biết dùng phạm vi nào nếu có nhiều hơn một.

Một số điểm bổ sung cần nhớ:

• Nếu bỏ qua area_num, Excel mặc định dùng phạm vi đầu tiên.

• Bạn không thể trộn phạm vi từ các trang tính khác nhau ở dạng này; sẽ gây lỗi.

• Nếu bỏ qua cả hàng và cột, hàm trả về toàn bộ vùng đã chỉ định.

Hàm MATCH() trong Excel

Hàm MATCH() giúp bạn tìm vị trí của một giá trị trong một hàng hoặc cột. Thay vì trả về giá trị, nó cho biết vị trí của giá trị đó trong danh sách.

Cú pháp là:

MATCH(lookup_value, lookup_array, [match_type])Được hỗ trợ bởi

Giải thích các đối số:

• lookup_value (bắt buộc) là giá trị bạn muốn tìm. Có thể là số, văn bản, giá trị logic (TRUE/FALSE), hoặc tham chiếu ô.

• lookup_array (bắt buộc) là phạm vi ô Excel sẽ tìm kiếm.

• match_type (tùy chọn) xác định cách Excel tìm khớp. Mặc định là 1. Trong đó 1 tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value (cần sắp xếp tăng dần). 0 tìm khớp chính xác (bất kỳ thứ tự nào). -1 tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value (cần sắp xếp giảm dần).

Hàm MATCH không phân biệt chữ hoa chữ thường. Nếu bạn làm việc với văn bản và muốn dùng ký tự đại diện như * (bất kỳ số lượng ký tự) hoặc ? (một ký tự), hãy đặt match_type là 0. Vì đó là chế độ duy nhất hỗ trợ tìm kiếm bằng ký tự đại diện.

INDEX() và MATCH() phối hợp thế nào trong Excel?

Một trong các cách dùng phổ biến của MATCH() là kết hợp với hàm INDEX(). Trong khi MATCH() tìm vị trí của một giá trị, INDEX() lấy giá trị tại vị trí đó. 

Lồng MATCH() vào INDEX() giúp công thức linh hoạt và bền vững khi cột hoặc hàng thay đổi theo thời gian. 

Ví dụ, tôi có một tập dữ liệu mẫu. Nếu tôi muốn tìm giá trị ở cột B cùng hàng với Banana ở cột A, tôi có thể dùng INDEX MATCH như sau:

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))Được hỗ trợ bởi

Ở đây, MATCH("Banana", A2:A5, 0) tìm vị trí của Banana trong cột A và INDEX(B2:B5, ...) trả về giá trị từ cùng hàng ở cột B.

INDEX MATCH so với VLOOKUP(): Nên dùng cái nào?

INDEX MATCH có thể làm được những việc mà VLOOKUP() không thể. Tuy nhiên, lựa chọn giữa chúng phụ thuộc vào mức độ linh hoạt và hiệu năng bạn cần trong các trang tính Excel. Hãy so sánh.

Tính linh hoạt theo cột và sự toàn vẹn dữ liệu

Một trong những hạn chế lớn của VLOOKUP() là phụ thuộc vào số cột cố định. Nếu bạn chèn hoặc di chuyển cột, công thức có thể sai.

Ví dụ sau:

Nếu một cột mới được thêm giữa Cột A và Cột B, cột thứ ba sẽ dịch chuyển và công thức trả về kết quả sai, như hình dưới.

=VLOOKUP("Apple", A2:C5, 2, FALSE)Được hỗ trợ bởi

INDEX MATCH tránh được vấn đề này bằng cách làm việc với phạm vi xác định thay vì vị trí cột cố định:

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))Được hỗ trợ bởi

Ngay cả khi các cột bị sắp xếp lại, công thức này vẫn hiệu quả, giúp đáng tin cậy và dễ bảo trì hơn. Ở đây, một cột được chèn trước cột B; công thức được cập nhật tự động và kết quả vẫn đúng. Đây là điểm mạnh của INDEX MATCH.

Hướng tra cứu và tính linh hoạt

VLOOKUP() chỉ có thể tìm từ trái sang phải. Nghĩa là cột tra cứu luôn phải là cột đầu của phạm vi. Nếu giá trị cần tìm nằm bên trái cột tra cứu, VLOOKUP() không thể tìm thấy.

Tuy nhiên, nếu tra cứu theo tên trái cây để tìm màu ở bên phải, công thức này sẽ hoạt động. Ví dụ, nếu muốn tìm màu của chuối trong danh sách, công thức cho kết quả chính xác: 

Với INDEX MATCH, hạn chế đó không tồn tại. Bạn có thể tra cứu theo mọi hướng: trái, phải, lên, xuống. Nó cũng hỗ trợ tra cứu ngang, giúp bạn kiểm soát tốt hơn cách tổ chức dữ liệu. 

Hãy áp dụng INDEX MATCH cho ví dụ này để thấy cách nó cho phép tra cứu cả bên trái và bên phải.

Với tra cứu sang trái, bạn có thể dùng công thức sau và nhận kết quả chính xác: 

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))Được hỗ trợ bởi

Với tra cứu sang phải, bạn có thể dùng công thức sau và nhận kết quả đúng: 

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))Được hỗ trợ bởi

Hiệu năng và hiệu quả trong Excel

VLOOKUP() quét toàn bộ bảng để tìm khớp và trả về giá trị trong một lần. Còn INDEX MATCH chia nhỏ tác vụ: MATCH() tìm hàng hoặc cột, và INDEX() lấy giá trị. 

Điều này giúp nhanh hơn và tiết kiệm bộ nhớ hơn, đặc biệt trong sổ làm việc phức tạp hoặc khối lượng lớn.

Bảng sau giúp bạn hình dung hàm nào hỗ trợ chức năng nào:

Khi nào nên dùng INDEX MATCH

Bạn có thể dùng INDEX MATCH khi:

• Làm việc với tập dữ liệu lớn, nơi tốc độ quan trọng
• Mô hình của bạn linh hoạt, các cột thường được thêm hoặc sắp xếp lại
• Bạn cần tra cứu các giá trị ở bên trái hoặc trong các phạm vi ngang

Nếu tệp Excel nhỏ và đơn giản, VLOOKUP() vẫn có thể đáp ứng. Nhưng với mô hình và báo cáo nâng cao, INDEX MATCH là lựa chọn an toàn hơn.

Cách dùng INDEX MATCH từng bước

Giả sử bạn có hai cột: Fruits và Sales. Bạn muốn tìm giá trị doanh số cho “Apple”.

Để làm điều này, nhập công thức kết hợp INDEX MATCH và nhấn Enter:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Được hỗ trợ bởi

Ở đây, MATCH("Apple", A2:A6, 0) tìm vị trí của “Apple” trong danh sách Fruits . Và INDEX(B2:B6, ...) dùng vị trí đó để trả về giá trị doanh số tương ứng từ cột Sales.

Bạn có thể thấy công thức trả về 12000, là doanh số của Apple. Đó là kết quả của bạn.

Tránh lỗi với INDEX MATCH

Dù INDEX MATCH là bộ đôi mạnh mẽ, vẫn có thể phát sinh lỗi nếu thiết lập không đúng. Dưới đây là cách khắc phục các vấn đề phổ biến và tránh sai sót có thể làm hỏng công thức.

Lỗi #N/A và cách xử lý

Lỗi #N/A nghĩa là Excel không tìm thấy giá trị bạn cần. Một số nguyên nhân thường gặp:

• Lỗi chính tả, thừa khoảng trắng, hoặc không khớp văn bản (ví dụ "apple" so với "Apple")

• Không có giá trị trong phạm vi tra cứu

• Sai match_type, ví dụ dùng 1 thay vì 0 cho khớp chính xác

• Ký tự ẩn (như khoảng trắng không ngắt) trong dữ liệu

Có hai cách để khắc phục:

Dùng TRIM() để loại bỏ khoảng trắng thừa ở đầu/cuối:

=MATCH(TRIM("Apple"), A2:A6, 0)Được hỗ trợ bởi

Thêm IFERROR() để hiển thị giá trị dự phòng thay vì lỗi:

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")Được hỗ trợ bởi

Lỗi #REF! trong công thức INDEX MATCH

Lỗi #REF! thường chỉ tham chiếu bị hỏng—thường do:

• Không khớp về số hàng giữa phạm vi INDEX() và MATCH()

• Xóa hàng hoặc cột được dùng trong công thức

• Sao chép/dán công thức mà không cập nhật tham chiếu ô

Có một số cách đơn giản để khắc phục: 

• Đảm bảo phạm vi của INDEX() và MATCH() có cùng kích thước:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Được hỗ trợ bởi

• Cố định phạm vi bằng tham chiếu tuyệt đối (ví dụ $A$2:$A$6) để giữ công thức ổn định khi sao chép.

• Tránh xóa các ô mà công thức đang phụ thuộc.

Hiệu năng chậm và tập dữ liệu lớn

Nếu bạn làm việc với hàng nghìn hàng, công thức INDEX MATCH chưa tối ưu có thể làm Excel chậm.

Cách tối ưu:

• Giới hạn phạm vi tra cứu thay vì tham chiếu cả cột:

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))Được hỗ trợ bởi

• Tránh dùng các hàm dễ biến động như OFFSET(), INDIRECT() và NOW() gần khu vực tra cứu.

• Dùng Excel Tables hoặc phạm vi có tên để tổ chức tốt hơn và cải thiện hiệu năng.

Xử lý lỗi tràn (spill) trong Excel 365

Excel 365 giới thiệu mảng động, có thể “tràn” kết quả sang các ô kề. Nếu quá trình này gặp vấn đề, bạn sẽ nhận lỗi #SPILL!.

Một số nguyên nhân thường gặp:

• Các ô kề không trống.
• Công thức trả về nhiều giá trị vào phạm vi ô bị chặn hoặc hợp nhất.
• Bạn đang dùng mảng động khi chỉ cần một giá trị đơn.

Các vấn đề này có thể khắc phục. Cách xử lý:

• Xóa nội dung các ô bên dưới hoặc bên cạnh công thức.
• Bỏ gộp các ô trong vùng tràn.
• Nếu bạn chỉ muốn một giá trị, ép trả về một kết quả đơn với @:

=@INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Được hỗ trợ bởi

Tổng kết

Nếu bạn đã quen dùng VLOOKUP(), giờ là lúc chuyển sang thứ ổn định hơn. Bạn có thể bắt đầu với vài phép tra cứu đơn giản bằng INDEX MATCH trên bản sao tệp làm việc. Thử thay thế công thức mới ở những nơi thay đổi cột gây lỗi hoặc nơi cấu trúc dữ liệu đòi hỏi linh hoạt. Khi thấy nó xử lý gọn gàng các tình huống đó, bạn sẽ khó quay lại cách cũ.