HÀM INDEX VÀ HÀM MATCH TRONG EXCEL- SỰ THAY THẾ TỐT HƠN CẢ HÀM VLOOKUP

Bài viết này diễn giải ưu điểm chính của hàm INDEX và hàm MATCH trong Excel – các ưu điểm khiến nó phù hợp hơn so với hàm VLOOKUP. Bạn sẽ tìm thấy nhiều ví dụ công thức – những ví dụ giúp bạn dễ dàng xử lý các nhiệm vụ phức tạp khi hàm VLOOKUP không làm được.

Trong các bài viết gần đây tôi đã cố gắng hết mình để giải thích những điểm cơ bản của hàm VLOOKUP trong Excel cho người mới bắt đầu và cung cấp các ví dụ công thức hàm VLOOKUP nâng cao. Và bây giờ, tôi sẽ cố gắng nếu đã không nói về việc sử dụng hàm VLOOKUP, thì ít nhất sẽ cung cấp cách thay thế để thực hiện phép tìm kiếm theo cột trong Excel.

“Tôi cần nó để làm gì?” bạn có thể hỏi tôi như thế. Bởi vì hàm VLOOKUP không phải là hàm tìm kiếm duy nhất có sẵn trong Excel, và việc có nhiều hạn chế có thể ngăn cản bạn đạt được kết quả mong muốn trong nhiều tình huống. Mặt khác, hàm INDEX và hàm MATCH trong Excel thì linh hoạt hơn và có những tính năng nhất định khiến chúng vượt trội hơn hàm VLOOKUP trên nhiều phương diện.

HÀM INDEX VÀ HÀM MATCH TRONG EXCEL – NHỮNG ĐIỀU CƠ BẢN

Vì mục đích của bài viết này là để nói về cách tìm kiếm thay thế trong Excel bằng cách kết hợp hàm INDEX với hàm MATCH, nên chúng ta sẽ không nói nhiều về cú pháp và cách sử dụng. Chúng ta sẽ chỉ nói đủ để hiểu ý tưởng chung rồi nói kỹ hơn về các ví dụ công thức – các ví dụ cho thấy ưu điểm của việc sử dụng hàm INDEX và hàm MATCH thay cho hàm VLOOKUP.

CÚ PHÁP VÀ CÁCH DÙNG CỦA HÀM INDEX:

Hàm INDEX trong Excel trả về giá trị từ mảng dựa trên số thứ tự hàng và cột bạn xác định. Cú pháp hàm INDEX rất đơn giản:

INDEX(array, row_num, [column_num])

Dưới đây là cách giải thích đơn giản cho mỗi thông số:

array – đây là dải ô nơi bạn muốn giá trị được trả về.

row_num – số thứ tự của hàng trong mảng mà bạn muốn giá trị được trả về. Nếu bỏ trống, thì colum_num là bắt buộc.

colum_num – số thứ tự của cột trong mảng mà bạn muốn giá trị được trả về. Nếu bỏ trống, thì row_num là bắt buộc.

Nếu cả thông số row_num và colum_num đều được sử dụng, thì hàm INDEX sẽ trả về giá trị ở ô là giao điểm của hàng và cột xác định.

Và đây là ví dụ đơn giản nhất của hàm INDEX:

=INDEX(A1:C10,2,3)

Công thức tìm kiếm dải ô A1:C10 rồi trả về giá trị của ô ở hàng thứ hai và cột thứ ba, cụ thể là ô C2.

Rất dễ, đúng không? Tuy nhiên, khi xử lý dữ liệu thực, bạn hầu như không biết được mình muốn hàng nào cột nào, đây là lý do bạn cần sự giúp đỡ của hàm MATCH.

CÚ PHÁP VÀ CÁCH DÙNG CỦA HÀM MATCH:

Hàm MATCH trong Excel tìm kiếm giá trị cần tìm trong dải ô, rồi trả về vị trí tương đối của ô đó trong dải ô.

Ví dụ, nếu dải ô B1:B3 chứa các giá trị “New-York”, “Paris”, “London”, rồi công thức =MATCH(“London”,B1:B3,0) trả về số 3 bởi vì “London” là mục thứ ba trong dải ô.

Cú pháp hàm MATCH có dạng dưới đây:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value – đây là chữ sô hay chuỗi ký tự mà bạn tìm kiếm. Đây có thể là một giá trị, một tham chiếu ô hay một giá trị lô gic.

lookup_array – dải ô được tìm kiếm.

match_type – thông số này nói cho hàm MATCH biết bạn muốn trả về sự phù hợp tuyệt đối hay sự phù hợp tương đối.

1 hay bỏ trống – tìm giá trị lớn nhất – giá trị nhỏ hơn hay bằng giá trị cần tìm. Các giá trị trong mảng cần tìm phải được lọc theo thứ tự tăng dần, cụ thể là từ nhỏ nhất đến lớn nhất.

0 – tìm giá trị đầu tiên bằng giá trị cần tìm. Trong sự kết hợp của hàm INDEX và hàm MATCH, bạn hầu như luôn luôn cần sự phù hợp tuyệt đối, vì thế câu lệnh thứ ba của hàm MATCH là “0”.

-1 – tìm giá trị nhỏ nhất lớn hơn hay bằng giá trị cần tìm. Các giá trị trong mảng cần tìm phải được sắp xếp theo thứ tự giảm dần, cụ thể là từ lớn nhất đến nhỏ nhất.

Lúc đầu, tính hữu ích của hàm MATCH có thể không rõ ràng. Ai quan tâm đến vị trí của giá trị trong dải ô chứ? Điều ta thật sự muốn biết đó chính là giá trị.

Hãy để tôi nhắc bạn nhớ rằng vị trí tương đối của giá trị cần tìm (cụ thể là số thứ tự của cột hay/và hàng) chính là tất cả những gì bạn cần để nhập vào câu lệnh row_num hay/và column_num của hàm INDEX. Có thể bạn vẫn còn nhớ, hàm INDEX có thể trả về giá trị ở giao điểm của hàng và cột xác định, nhưng nó không thể xác định một cách chính xác bạn muốn cột nào hàng nào.

Xem thêm: Hướng dẫn học Excel cơ bản

CÁCH SỬ DỤNG HÀM INDEX MATCH TRONG EXCEL:

Bây giờ bạn đã biết những điều cơ bản của hai hàm trên, tôi tin rằng bạn có thể mường tượng hai hàm này sẽ kết hợp như thế nào.

Hàm MATCH xác định vị trí tương đối của giá trị cần tìm trong dải ô xác định. Từ đó, hàm INDEX sử dụng con số đó, hay các con số đó, rồi trả về giá trị ở ô tương ứng.

Vẫn gặp khó khăn trong việc mường tượng ra đúng không? Hãy nghĩ về hàm INDEX/MATCH theo cách này:

=INDEX (column to return a value from, MATCH (lookup value, column to lookup against, 0))

Tôi tin rằng việc xem ví dụ sẽ dễ hiểu hơn. Giả sử bạn có một danh sách các thủ đô như thế này:

Hãy tìm dân số của một thủ đô nào đó, giả sử thủ đô của Nhật Bản đi, hãy dùng công thức INDEX/MATCH dưới đây:

=INDEX($D$2:$D$10,MATCH(“Japan”,$B$2:$B$10,0))

Bây giờ, hãy phân tích xem mỗi thành phần của công thức có tác dụng gì:

Hàm MATCH tìm kiếm giá trị cần tìm “Nhật Bản” ở cột B, chính xác hơn là dải ô B2:B10,  rồi trả về số 3, bởi vì “Nhật Bản” nằm thứ ba trong danh sách.

Hàm INDEX lấy số 3 từ thông số thứ hai (row_num), thông số này chỉ rõ bạn muốn trả về giá trị từ hàng nào, rồi trở thành công thức đơn giản như sau =INDEX($D$2:$D$10,3). Nói một cách đơn giản, công thức được đọc như sau: tìm trong dải ô D2:D10 rồi trả về giá trị của ô ở hàng thứ ba, cụ thể là ô D4, bởi vì chúng ta bắt đầu đếm từ hàng thứ hai.

Và đây là kết quả bạn có được trong Excel:

Quan trọng! Số hàng và số cột trong mảng INDEX nên lần lượt khớp với số hàng và số cột trong thông số row_num hay/và column_num trong hàm MATCH. Nếu không thì, công thức sẽ trả về kết quả không chính xác.

Đợi đã…tại sao chúng ta không dùng công thức VLOOKUP sau? Mục đích của việc lãng phí thời gian cố gắng tìm kiếm sự thay thế bí ẩn của hàm INDEX/MATCH trong Excel là gì?

=VLOOKUP(“Japan”,$B$2:$D$2,3)

Trong trường hợp này, chẳng có mục đích nào cả