Ở 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, Gitiho sẽ cho bạn thấy chúng được ứng dụng thế nào trong các ví dụ nhé. Show 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áiNhượ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. 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:
Những điều cần ghi nhớ khi sử dụng hàm AVERAGE với hàm INDEX/MATCHVớ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í 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ộtNhư đã 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.
=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ì “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 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 đó,
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 ý:
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 đó,
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
Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Nâng Cao |
