Cách dùng hàm INDEX MATCH nhiều điều kiện nâng cao (Phần 3)

Tiếp nối bài viết 2 phần trước về ứng dụng của hàm INDEX và MATCH nâng cao, hôm nay Gitiho sẽ tiếp tục cho bạn thấy ứng dụng của hàm INDEX MATCH nhiều điều kiện để tìm kiếm với nhiều tiêu chí, tìm kiếm giá trị bên trái, theo hàng và cột và các cách kết hợp với hàm khác trong các ví dụ cụ thể nhé.

Cách sử dụng hàm INDEX MATCH 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, kết hợp hàm INDEX MATCH nhiều điều kiện thay cho VLOOKUP 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 hàm INDEX MATCH nhiều điều kiện 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)

Giải thích công thức:

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 giá trị cần tìm lookup_value lại bằng 1?

Bạn cần kiểm tra xem giá trị đầu tiên (A2) trong cột 'Tên khách hàng' trong Bảng cần điền có tồn tại trong danh sách tên khách hàng của Bảng tra cứu (A2:A13) hay không. Nếu có, công thức sẽ trả về TRUE, còn không thì trả về FALSE. Sau đó, bạn sẽ 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ề cách mà hàm MATCH hoạt động trong công thức này nhé. 

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

Giá trị TRUE tương đương với 1, FALSE tương đương với 0, 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 giá trị “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à 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 ý:

• Tham số thứ cuối cùng (colum_num) của hàm INDEX là 3 vì chúng ta cần lấy giá trị từ cột thứ 3 ở bảng tra cứu vào cột D.
• Nhấn Ctrl+Shift+Enter để hoàn thiện công thức sau khi bạn đã nhập xong.

Bạn hoàn toàn có thể nhìn thấy kết quả khi dùng hàm INDEX MATCH nhiều điều kiện như hình ảnh trên. Khi sử dụng thành thạo, thì bạn có thể giải các bài tập với hàm index và match 3 điều kiện, hoặc nhiều hơn thế tương tự.

Tìm kiếm giá trị bên trái với hàm INDEX MATCH

Nhược điểm của hàm VLOOKUP là không thể tìm kiếm giá trị 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''

Bước 1. Điền công thức dùng hàm MATCH tìm vị trí của Nga:

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

Bước 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)

Bước 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))

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.

Xem thêm: CÁCH ỨNG DỤNG HÀM INDEX VÀ MATCH NÂNG CAO (PHẦN 1)

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

Bạn có thể lồng các hàm khác vào hàm INDEX MATCH để 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:

Để hiểu bản chất các hàm Excel , cách sử dụng và viết công thức, mời bạn đọc tham khảo khóa học sau của Gitiho:

EXG05 - Kỹ năng sử dụng công thức và hàm A-Z trong Excel

G-LEARNING

499,000^đ 799,000^đ

Đăng ký Học thử

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

Cú pháp hàm INDEX cũng cho phép thực hiện tra cứu ở giao điểm của cột và hàng. Công thức INDEX và MATCH để trả về số thứ tự của cột như sau:

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

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 2021.

Bước 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.

=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 đề).

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

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

Bước 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: 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.

Hàm IFERROR kết hợp INDEX và MATCH

Đô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 (''").

Kết luận:

Hy vọng qua 3 phần của bài viết này, bạn đọc đã hiểu được cách sử dụng kết hợp hàm INDEX và hàm MATCH từ cơ bản đến nâng cao như hàm INDEX MATCH nhiều điều kiện, hay những ứng dụng tuyệt với khi kết hợp hàm IF và INDEX MATCH, hoặc IFERROR,…

Đừng quên luyện tập các bài học Excel online 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!