.st0{fill:#FFFFFF;}

CÁCH ỨNG DỤNG HÀM INDEX VÀ MATCH NÂNG CAO (PHẦN 3) 

 Tháng Chín 21, 2021

By  Lê Đình Chi

Ở 2 phần trước của bài viết, lý thuyết cơ bản về hàm INDEX và hàm MATCH hay sự kết hợp của 2 hàm này trong cùng 1 công thức đã được giới thiệu. Tiếp nối bài viết, Zik.vn sẽ cho bạn thấy chúng được ứng dụng thế nào trong các ví dụ nhé.

Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ

Sau khi đã nắm vững ký thuyết, việc có thể áp dụng chúng vào công việc hay các bài tập thực tế mới chính là chìa khóa giúp bạn hiểu và sử dụng các hàm này.

Tìm kiếm giá trị bên trái

Nhược điểm của hàm VLOOKUP, như đã giới thiệu trong nhiều bài viết, chính là không thể tìm kiếm bên trái của nó. Do đó, nếu cột bạn cần tra cứu không phải là cột cận trái trong dải ô cần tìm, thì kết quả mà bạn mong muốn sẽ không thể trả về bằng hàm VLOOKUP.

Nhưng với INDEX MATCH, hoạt động linh hoạt hơn và thật sự hàm không quan tâm vị trí mà cột trả kết quả. 

Ví dụ: Bảng dưới đây có các thông tin về quốc gia, thủ đô của chúng và dân số trong thủ đô này. Hãy thiết lập công thức tra cứu thứ tự xếp hạng của ”Nga”


1. Bạn cần lập công thức dùng hàm MATCH tìm vị trí của Nga:

=MATCH(“Nga”,$B$2:$B$10,0))

2. Xác định thông số cho hàm INDEX, tại ví dụ này, thông số là cột A (dải ô A2:A10)

3. Kết hợp hai bước trên lại, bạn có công thức sau:

=INDEX($A$2:$A$10,MATCH(“Nga”,$B$2:$B$10,0))

Tips (Mẹo)Việc sử dụng tham chiếu ô tuyệt đối luôn là một ý tưởng hay trong công thức INDEX và MATCH để dải ô cần tìm của bạn không bị phá hỏng khi bạn sao chép công thức sang ô khác.

Tính toàn bằng hàm INDEX MATCH (AVERAGE, MAX, MIN)

Với các sử dụng như trên, việc tính toán trong Excel trở nên đơn giản khi bạn hoàn toàn có thể lồng các hàm khác vào hàm INDEX MATCH như để tìm giá trị nhỏ nhất hay lớn nhất, hay giá trị gần với giá trị trung bình nhất trong dải. Dưới đây là một số ví dụ công thức cho bảng được dùng trong ví dụ trước:

Chức năng
Ví dụ công thức
Miêu tả
Kết quả được trả về
Min =INDEX($C$2:$C$10, MATCH(MIN($D$2:I$10), $D$2:D$10, 0)) Tìm kiếm giá trị nhỏ nhất trong cột D rồi lấy một giá trị từ cột C trên cùng một hàng. Bắc Kinh
Max =INDEX($C$2:$C$10, MATCH(MAX($D$2:I$10), $D$2:D$10, 0)) Tìm kiếm giá trị lớn nhất ở cột D rồi lấy một giá trị từ cột C trên cùng một hàng. Lima
Average =INDEX($C$2:$C$10, MATCH(AVERAGE($D$2:D$10), $D$2:D$10, 1)) Tính giá trị trung bình trong dải ô D2:D10, tìm giá trị gần với giá trị trung bình nhất, rồi lấy giá trị tương ứng từ cột C. Matxcơva

Những điều cần ghi nhớ khi sử dụng hàm AVERAGE với hàm INDEX/MATCH

Với tham số thứ 3 (match_type) của hàm MATCH, khi kết hợp hàm AVERAGE với hàm INDEX và MATCH, thường là “1” hay “-1” . Nhưng nếu bạn không chắc mảng cần tìm có chứa giá trị bằng giá trị trung bình hay không, thì bạn có thể nhập “0” cho sự phù hợp tuyệt đối.

  • Với 1: Các giá trị trong cột cần tìm phải được sắp xếp theo thứ tự tăng dần, và công thức sẽ trả về giá trị lớn nhất – giá trị này có thể nhỏ hơn hay bằng giá trị trung bình.
  • Với -1: Các giá trị trong cột cần tìm phải được sắp xếp theo thứ tự giảm dần, và công thức sẽ trả về giá trị nhỏ nhất – giá trị này có thể lớn hơn hay bằng giá trị trung bình.

Ví dụ: Vẫn trong ví dụ của phần trên, các giá trị trong cột D được sắp xếp theo thứ tự tăng dần, nên tham số thứ 3 bạn cần điền là ”1”

Tìm kiếm giá trị theo hàng và theo cột

Như đã biết trước, hàm VLOOKUP hai chiều cho phép người dùng tra cứu ở giao điểm của cột và hàng. Nhưng có lẽ bạn chưa biết, cú pháp hàm INDEX cũng cho phép thực hiện cả 2 nhiệm vụ này. Công thức INDEX và MATCH đã được giới thiệu ở các phần trước kết hợp thêm một hàm MATCH vào nó, việc này sẽ trả về số thứ tự của cột.

Công thức như sau:

=INDEX (lookup table, MATCH (vertical lookup value, column to lookup against, 0), MATCH (horizontal lookup value, row to lookup against, 0))

Lưu ý: Phải định rõ toàn bộ bảng trong câu lệnh mảng của hàm INDEX trong trường hợp tìm kiếm theo hai cách.

Ví dụ: Bảng dưới đây có thể hiện danh sách các thành phố đông dân nhất thế giới. Hãy tra cứu dân số của Mỹ năm 2015.

1. Viết 2 hàm MATCH trả về số thứ tự hàng và cột cho hàm INDEX của bạn.

  • Vertical match – bạn dò cột B, chính xác là từ ô B2 đến ô B11, để kiếm giá trị ở ô H3 (“Mỹ”), và hàm MATCH tương ứng có dạng như sau:

=MATCH($H$3,$B$1:$B$11,0)

Công thức MATCH này trả về giá trị 4 vì “Mỹ” là giá trị thứ tư ở cột B (bao gồm cột tiêu đề).

  • Horizontal match – bạn tìm giá trị ở cột H4 (“2021”) từ hàng 1, cụ thể là từ ô A1 đến ô E1:

=MATCH($H$4,$A$1:$E$1,0)

Công thức MATCH này trả về số “5” bởi vì “2015” nằm ở cột thứ năm.

2. Đặt các công thức trên vào hàm INDEX như sau:

=INDEX($A$1:$E$11, MATCH($H$3,$B$1:$B$11,0), MATCH($H$4,$A$1:$E$1,0))

Mẹo (Tips): Nếu bạn thay thế các hàm MATCH bằng các số được trả về, thì công thức sẽ dễ hiểu hơn rất nhiều:

=INDEX($A$1:$E$11, 4, 5, 0))

Có nghĩa là, nó trả về giá trị ở giao điểm của hàng thứ tư và cột thứ năm trong dải ô A1:E11, giá trị đó nằm ở ô E4

Tìm kiếm theo nhiều điều kiện

VLOOKUP cũng là hàm cho phép bạn tìm kiếm theo nhiều điều kiện, tuy nhiên bạn cần có một cột trợ giúp, đây cũng chính là nhược điểm khi sử dụng hàm này. Để khắc phục hạn chế này, hàm INDEX và MATCH là sự lựa chọn hoàn hảo giúp bạn tìm kiếm ở 2 cột mà không cần bất kỳ cột trợ giúp nào.

Ví dụ: Danh sách sau đây là một đơn hàng, yêu cầu tính tổng dựa trên 2 tiêu chí ”Tên khách hàng” và ”Sản phẩm”. Trong đó, một khách hàng có thể mua nhiều sản phẩm và tên khách hàng ở đây được liệt kê ngẫu nhiên trong bảng tra cứu mà bạn cần điền.

Công thức cần điền:

=INDEX(‘Bảng tra cứu’!$A$2:$C$13,MATCH(1,(A2=’Bảng tra cứu’!$A$2:$A$13)*(B2=’Bảng tra cứu’!$B$2:$B$13),0),3)

Công thức này phức tạp hơn rất nhiều công thức được giới thiệu trước. Hãy cùng nhau xây dựng công thức nhé.

Bắt đầu từ phần hàm MATCH, do đây là phần khó nhất:

MATCH(1,(A2=’Bảng tra cứu’!$A$2:$A$13)*(B2=’Bảng tra cứu’!$B$2:$B$13),0)

Trong đó,

  • Lookup_value: 1
  • Lookup_array: (A2=’Bảng tra cứu’!$A$2:$A$13)*(B2=’Bảng tra cứu’!$B$2:$B$13)
  • Match_type: 0

Tại sao lại cần tìm giá trị 1?

Bạn cần tìm giá trị đầu tiên (A2) trong cột ”Tên khách hàng” trong Bảng cần điền và tìm tên đó trong tất cả tên khách hàng ở Bảng tra cứu (A2:A13). Nếu tìm thấy sự trùng khớp, thì phương trình trả về TRUE, nếu không thì sẽ trả về FALSE. Rồi bạn làm tương tự với các giá trị ở cột B (“Sản phẩm”).

Hãy cùng nhau làm phép thử để hiểu rõ hơn về sự giải thích này nhé.

Thao tác: Chọn mảng (Lookup_array), sau đó nhấn phím F9. Bạn được kết quả hiển thị sau:

Như đã từng giới thiệu trước đây, giá trị TRUE tương đương với 1, FALSE tương đương với 0. Và, dấu hoa thị (*) đóng vai trò phép toán AND trong công thức nên chúng ta nhận được “1” chỉ khi sự phù hợp được tìm thấy ở cả hai cột, nếu không thì chúng ta nhận được “0”.

Vì thế, kết quả của thao tác này là một mảng gồm 1 và 0, trong đó “1” là giá trị đáp ứng cả hai điều kiện xác định. Nếu bảng cần tìm của bạn không có bất kỳ hàng nào trùng lặp nhau, thì sẽ chỉ có một giá trị “1” trong mảng. Và vì “1” là giá trị cần tìm của chúng ta, nên hàm MATCH trả về vị trí tương đối của hàng đó

Lưu ý:

  • Bạn phải sử dụng tham số thứ 3 (colum_num) của hàm INDEX. Vì bạn đã định rõ toàn bộ bảng ở tham số thứ nhất (array), và bạn cần phải để hàm biết bạn muốn lấy giá trị của cột nào. Trong ví dụ này, đó là cột C (“Tổng”), nên nhập 3 trong câu lệnh thứ ba của hàm INDEX.
  • Nhấn Ctrl+Shift+Enter để hoàn thiện công thức sau khi bạn đã nhập xong.

Và bạn hoàn toàn có thể nhìn thấy kết quả như hình ảnh trên.

Kết hợp với hàm IFERROR

Đôi khi bạn sẽ gặp lỗi #N/A hay lỗi #VALUE và lỗi báo giá trị không tồn tại trong mảng cần tìm, để khắc phục lỗi này bằng cái gì đó có nghĩa, bạn có thể lồng hàm INDEX kết hợp MATCH trong hàm IFERROR.

Công thức hàm:

=IFERROR(value, value_if_error)

Trong đó, 

  • value: Là giá trị được kiểm tra lỗi (kết quả của công thức INDEX MATCH trong trường hợp này)
  • value_if_error: Là giá trị cần trả về nếu công thức có lỗi.

Ví dụ: Thử nhập công thức dưới đây vào bảng:

=IFERROR(INDEX($A$1:$E$11, MATCH($G$2,$B$1:$B$11,0), MATCH($G$3,$A$1:$E$1,0)),“Không tìm thấy. Xin hãy thử lại!”)


Lưu ý: Khi không muốn điền gì cả hoặc muốn để trống ô mà không tìm thấy giá trị tra cứu, bạn chỉ cần thay cụm từ (“Không tìm thấy. Xin hãy thử lại!”) thành (””).

Hy vọng qua 3 phần của bài viết này, từ cơ bản đến nâng cao, bạn đọc đã hiểu được cách sử dụng kết hợp hàm INDEX và hàm MATCH và hàm khác trong Excel. Đừng quên luyện tập các bài tập Excel có đáp án và tìm hiểu thêm một số hàm khác như VLOOKUP, IF, AND,… để có thể thành thạo kiến thức này ứng dụng vào công việc của bạn nhé!

Hẹn gặp lại bạn đọc trong bài viết tiếp theo!

Bài viết tham khảo khác:

HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ

Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Lê Đình Chi


Your Signature

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Subscribe to our newsletter now!

DMCA.com Protection Status
>