Ở Hai 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 Hai hàm này trong cùng Một công thức đã được giới thiệu. Tiếp nối bài viết, Gitiho sẽ cho bạn thấy chúng được ứng dụng thế nào trong những ví dụ nhé.
Sau lúc đã nắm vững ký thuyết, việc sở hữu thể vận dụng chúng vào công việc hay những 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 những hàm này.
Tìm kiếm trị giá 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à ko thể tìm kiếm bên trái của nó. Do đó, nếu cột bạn cần tra cứu ko 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ẽ ko 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 ko quan tâm vị trí mà cột trả kết quả.
Ví dụ: Bảng dưới đây sở hữu những 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 sử dụng hàm MATCH tìm vị trí của Nga:
=MATCH(“Nga”,$B$2:$B$10,0))
Tính toàn bằng hàm INDEX MATCH (AVERAGE, MAX, MIN)
Với những sử dụng như trên, việc tính toán trong Excel trở nên đơn thuần lúc bạn hoàn toàn sở hữu thể lồng những hàm khác vào hàm INDEX MATCH như để tìm trị giá nhỏ nhất hay to nhất, hay trị giá sắp với trị giá 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 sử 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 trị giá nhỏ nhất trong cột D rồi lấy một trị giá 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 trị giá to nhất ở cột D rồi lấy một trị giá 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 trị giá trung bình trong dải ô D2:D10, tìm trị giá sắp với trị giá trung bình nhất, rồi lấy trị giá tương ứng từ cột C. | Matxcơva |
Những điều cần ghi nhớ lúc sử dụng hàm AVERAGE với hàm INDEX/MATCH
Với thông số thứ 3 (match_type) của hàm MATCH, lúc 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 ko chắc mảng cần tìm sở hữu chứa trị giá bằng trị giá trung bình hay ko, thì bạn sở hữu thể nhập “0” cho sự thích hợp tuyệt đối.
- Với 1: Những trị giá 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ề trị giá to nhất – trị giá này sở hữu thể nhỏ hơn hay bằng trị giá trung bình.
- Với -1: Những trị giá 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ề trị giá nhỏ nhất – trị giá này sở hữu thể to hơn hay bằng trị giá trung bình.
Ví dụ: Vẫn trong ví dụ của phần trên, những trị giá trong cột D được sắp xếp theo thứ tự tăng dần, nên thông số thứ 3 bạn cần điền là ''1''
Tìm kiếm trị giá theo hàng và theo cột
Như đã biết trước, hàm VLOOKUP hai chiều cho phép người sử dụng tra cứu ở giao điểm của cột và hàng. Nhưng có nhẽ bạn chưa biết, cú pháp hàm INDEX cũng cho phép thực hiện cả Hai nhiệm vụ này. Công thức INDEX và MATCH đã được giới thiệu ở những 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 sở hữu thể hiện danh sách những thành thị trấn đông dân nhất toàn cầu. Hãy tra cứu dân số của Mỹ năm 2015.
1. Viết Hai 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, chuẩn xác là từ ô B2 tới ô B11, để kiếm trị giá ở ô H3 (“Mỹ”), và hàm MATCH tương ứng sở hữu dạng như sau:
=MATCH($H$3,$B$1:$B$11,0)
Công thức MATCH này trả về trị giá 4 vì “Mỹ” là trị giá thứ tư ở cột B (bao gồm cột tiêu đề).
- Horizontal match – bạn tìm trị giá ở cột H4 (“2021”) từ hàng 1, cụ thể là từ ô A1 tới ô 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 những 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))
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 sở hữu một cột trợ giúp, đây cũng chính là nhược điểm lúc 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 xuất sắc giúp bạn tìm kiếm ở Hai cột mà ko 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 Hai tiêu chí ''Tên khách hàng'' và ''Sản phẩm''. Trong đó, một khách hàng sở hữu thể sắm 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é.
Khởi đầ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
Vì sao lại cần tìm trị giá 1?
Bạn cần tìm trị giá trước hết (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 ko thì sẽ trả về FALSE. Rồi bạn làm tương tự với những trị giá ở 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ảng giải 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, trị giá 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ỉ lúc sự thích hợp được tìm thấy ở cả hai cột, nếu ko 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 Một và 0, trong đó “1” là trị giá đáp ứng cả hai điều kiện xác định. Nếu bảng cần tìm của bạn ko sở hữu bất kỳ hàng nào trùng lặp nhau, thì sẽ chỉ sở hữu một trị giá “1” trong mảng. Và vì “1” là trị giá 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 thông số thứ 3 (colum_num) của hàm INDEX. Vì bạn đã định rõ toàn bộ bảng ở thông số thứ nhất (array), và bạn cần phải để hàm biết bạn muốn lấy trị giá 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 lúc bạn đã nhập xong.
Và bạn hoàn toàn sở hữu thể nhìn thấy kết quả như hình ảnh trên.
Kết hợp với hàm IFERROR
Thỉnh thoảng bạn sẽ gặp lỗi #N/A hay lỗi #VALUE và lỗi báo trị giá ko tồn tại trong mảng cần tìm, để khắc phục lỗi này bằng dòng gì đó sở hữu nghĩa, bạn sở hữu 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à trị giá được rà soát lỗi (kết quả của công thức INDEX MATCH trong trường hợp này)
- value_if_error: Là trị giá cần trả về nếu công thức sở hữu 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)),“Ko tìm thấy. Xin hãy thử lại!”)
Lưu ý: Lúc ko muốn điền gì cả hoặc muốn để trống ô mà ko tìm thấy trị giá tra cứu, bạn chỉ cần thay cụm từ (“Ko tìm thấy. Xin hãy thử lại!”) thành (''").
Kỳ vọng qua 3 phần của bài viết này, từ cơ bản tới tăng, độc giả đã 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 tập dượt những bài tập Excel sở hữu đáp án và tìm hiểu thêm một số hàm khác như VLOOKUP, IF, AND,... để sở hữu thể thạo tri thức này ứng dụng vào công việc của bạn nhé!
Hứa gặp lại độc giả 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 sử dụng hàm VLOOKUP qua những ví dụ từ cơ bản tới tăng
Hướng dẫn cách copy chỉ những trị giá hiển thị sau lúc lọc trong Excel
