›09:55:42 05-02-2024 | Lượt xem: 1370
Một trong những tác vụ phổ biến nhất trong Excel là tra cứu các giá trị trong bảng dựa trên một số tiêu chí. Ví dụ: bạn có thể muốn tìm giá của sản phẩm, tên khách hàng hoặc số tiền bán hàng trong một tháng nhất định. Trước đây, người dùng Excel dựa vào hàm VLOOKUP cho công việc này. Tuy nhiên, VLOOKUP có khá nhiều hạn chế, khiến những người đam mê Excel hiểu biết phải tạo ra một giải pháp linh hoạt hơn bằng cách sử dụng kết hợp INDEX MATCH. Giờ đây, với sự ra đời của các phiên bản mới hơn, chúng ta có hàm XLOOKUP - một phiên bản kế thừa mạnh mẽ và linh hoạt hơn của VLOOKUP. Điều này đặt ra câu hỏi: liệu XLOOKUP không chỉ vượt trội hơn người tiền nhiệm của nó mà còn vượt qua sự kết hợp INDEX MATCH đã được thử và đúng? Chúng ta hãy đi sâu vào các tính năng và sắc thái của các chức năng này để tìm hiểu.
Trước tiên, hãy chia nhỏ cú pháp của các công thức này và hiểu cách chúng hoạt động trong Excel.
Sự kết hợp INDEX và MATCH đã vượt qua thử thách của thời gian như một phương pháp cổ điển cho nhiều kiểu tra cứu khác nhau trong Excel. Cú pháp như sau:
INDEX ( return_array , MATCH ( lookup_value , lookup_array , 0))
Ở đây, hàm MATCH xác định vị trí tương đối của giá trị tra cứu trong cột tra cứu, trong khi INDEX lấy giá trị tương ứng từ cột trả về tại vị trí đó. Đối số thứ ba của MATCH ( match_type ) được đặt thành 0 đảm bảo kết quả khớp chính xác, thường là hành vi mong muốn.
Mặc dù công thức này bao gồm hai hàm riêng biệt nhưng nó mang lại sự linh hoạt cao trong việc xử lý các tình huống tra cứu khác nhau.
Để biết thêm thông tin, hãy xem công thức INDEX & MATCH trong Excel.
Là một giải pháp thay thế hiện đại hóa cho MATCH, hàm XMATCH được giới thiệu đồng thời với XLOOKUP và chỉ có trong Excel 365 và Excel 2021. Dưới đây là những cải tiến chính:
Không giống như MATCH, XMATCH mặc định có kết quả khớp chính xác, phù hợp với hầu hết các trường hợp sử dụng.
XMATCH không yêu cầu sắp xếp mảng tra cứu để tìm kết quả khớp gần đúng.
Ngược lại với người tiền nhiệm của nó, XMATCH có thể tìm kiếm theo thứ tự ngược lại và thực hiện tìm kiếm nhị phân được tối ưu hóa về tốc độ. Hành vi này được kiểm soát bởi đối số search_mode tùy chọn .
Cú pháp trông như thế này:
INDEX ( return_array , XMATCH ( lookup_value , lookup_array , [match_mode], [search_mode])
Nói một cách ngắn gọn, INDEX XMATCH là một cách nâng cao hơn để thực hiện những việc tương tự như INDEX MATCH, cho phép bạn xử lý các vấn đề tra cứu phức tạp và đa dạng hơn. Trong hướng dẫn này, chúng ta sẽ coi các công thức này về cơ bản là có thể hoán đổi cho nhau, nhận ra rằng mỗi công thức đều có điểm mạnh riêng và có thể được ưu tiên sử dụng trong các bối cảnh khác nhau.
Hàm XLOOKUP, thường được gọi là "sát thủ" của VLOOKUP, thể hiện một cách tiếp cận hiện đại đối với các hoạt động tra cứu. Nó chỉ khả dụng cho người đăng ký Office 365 và người dùng Excel 2021, cung cấp một giải pháp thay thế mạnh mẽ. Cú pháp của XLOOKUP như sau:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Khắc phục nhiều hạn chế của VLOOKUP, XLOOKUP có thể xử lý nhiều tình huống khác nhau, bao gồm tra cứu bên trái, tra cứu ngang và dọc, tra cứu hai chiều, v.v. Tính linh hoạt của nó khiến nó trở thành lựa chọn phù hợp cho những người dùng muốn có kỹ thuật tra cứu toàn diện và hiệu quả trong các phiên bản Excel mới nhất.
Để biết thêm chi tiết, hãy xem Excel XLOOKUP với các ví dụ về công thức.
Cách bạn chọn để tra cứu dữ liệu trong Excel có thể tạo ra sự khác biệt lớn trong việc công việc của bạn diễn ra suôn sẻ như thế nào. Hãy cùng khám phá những điểm khác biệt để bạn có thể đưa ra lựa chọn tốt nhất cho các yêu cầu cụ thể của mình và loại dữ liệu bạn đang làm việc.
Dưới đây là một số khác biệt chính cần xem xét:
Và bây giờ, hãy thảo luận về các ví dụ thực tế để xem XLOOKUP có thể thay thế công thức INDEX MATCH lỗi thời như thế nào để giải quyết các vấn đề tra cứu khác nhau trong Excel.
Để minh họa quá trình chuyển đổi, hãy xem xét một ví dụ điển hình trong đó bạn có danh sách tên học sinh ở một cột và điểm thi tương ứng của họ ở một cột khác. Trong trường hợp này, bạn muốn lấy điểm của một học sinh cụ thể, chẳng hạn như trong ô E4.
Công thức INDEX MATCH cho tác vụ này sẽ như sau:
=INDEX(B4:B25, MATCH(E4, A4:A25, 0))
XLOOKUP cung cấp một giải pháp đơn giản không kém - chỉ cần cung cấp giá trị tra cứu, phạm vi tra cứu và phạm vi trả về được yêu cầu:
=XLOOKUP(E4, A4:A25, B4:B25)
Như bạn có thể thấy trong ảnh chụp màn hình, cả hai công thức đều đạt được kết quả như nhau:
Như bạn có thể biết, một trong những hạn chế lớn nhất của VLOOKUP là không có khả năng trả về giá trị từ các cột nằm ở bên trái cột tra cứu.
Cả XLOOKUP và INDEX MATCH đều vượt trội trong việc xử lý các tác vụ tra cứu bên trái, cung cấp giải pháp mà VLOOKUP thiếu. Đây là cách bạn có thể thực hiện tra cứu bên trái với từng phương pháp:
=INDEX(A4:A25, MATCH(E4, B4:B25, 0))
=XLOOKUP(E4, B4:B25, A4:A25)
Cả hai công thức đều tìm kiếm giá trị đã chỉ định (E4) trong cột B và trả về giá trị tương ứng từ cột A.
Trong các phiên bản Excel cũ hơn, hàm HLOOKUP được thiết kế đặc biệt để tìm kiếm theo hàng theo chiều ngang. Tin vui là cả XLOOKUP và INDEX MATCH đều thành thạo trong việc thực hiện tra cứu theo chiều ngang. Để đạt được điều này, bạn chỉ cần định hướng tra cứu và trả về mảng theo hàng thay vì cột.
=INDEX(B4:J4, MATCH(B8, B3:J3, 0))
=XLOOKUP(B8, B3:J3, B4:J4)
Khi thiết lập các trang tính thực, người dùng thường tham khảo toàn bộ cột hoặc hàng trong công thức của mình để tính đến những bổ sung dữ liệu tiềm năng trong tương lai. Cả XLOOKUP và INDEX MATCH đều sẵn sàng hỗ trợ phương pháp này. Ví dụ:
=INDEX(A:A, MATCH(E4, B:B, 0))
=XLOOKUP(E4, B:B, A:A)
Một lời cảnh báo cho những ai muốn sử dụng phương pháp này: tham chiếu toàn bộ cột hoặc hàng buộc Excel phải xử lý các mảng rất lớn. Điều này có thể ảnh hưởng đến hiệu suất và làm chậm bảng tính của bạn. Bạn phải luôn suy nghĩ xem điều này có thể ảnh hưởng như thế nào đến tốc độ tính toán và cố gắng giới hạn phạm vi dữ liệu của mình nếu hiệu suất là một vấn đề.
Trong trường hợp bạn cần tìm kiếm dữ liệu từ đầu đến cuối (từ dưới lên trên để tra cứu theo chiều dọc hoặc từ phải sang trái để tra cứu theo chiều ngang), XLOOKUP hoặc INDEX XMATCH có thể trợ giúp. Điều đáng lưu ý là hàm MATCH thiếu tính năng này và không thể áp dụng trong những tình huống như vậy.
Để tìm kiếm từ cuối đến đầu bằng XLOOKUP, bạn đặt đối số search_mode tùy chọn thành -1:
=XLOOKUP(F4, B4:B24, C4:C24, , ,-1)
Tương tự, đối với tìm kiếm ngược bằng INDEX XMATCH:
=INDEX(C4:C24, XMATCH(F4, B4:B24, ,-1))
Tra cứu hai chiều, còn được gọi là “ma trận” hoặc “tra cứu 2 chiều”, là một kỹ thuật tìm giá trị tại giao điểm của một hàng và cột cụ thể. Tổ hợp INDEX MATCH rất phù hợp cho kiểu tra cứu này vì INDEX có thể nhắm mục tiêu cả số hàng và số cột và bạn có thể sử dụng hai hàm MATCH khác nhau cho mục đích này.
Ví dụ: để biết điểm của một học sinh nào đó trong một môn học cụ thể, bạn có thể sử dụng công thức sau:
=INDEX(B4:D25, MATCH(G4, A4:A25, 0), MATCH(G5, B3:D3, 0))
Bây giờ, khi nói đến tra cứu 2D bằng XLOOKUP, nó phức tạp hơn một chút vì nó được thiết kế chủ yếu cho một mảng tra cứu duy nhất. Để xử lý đồng thời cả cột và hàng, bạn lồng một hàm XLOOKUP vào trong một hàm khác. Hàm bên trong tìm kiếm giá trị của nó và trả về một cột hoặc hàng dữ liệu liên quan. Mảng này sau đó trở thành return_array cho hàm bên ngoài. Đây là giao diện của nó:
=XLOOKUP(G4, A4:A25, XLOOKUP(G5, B3:D3, B4:D25))
Để biết thêm ví dụ về công thức, vui lòng xem Cách thực hiện tra cứu hai chiều trong Excel.
Cấu trúc của cả hai công thức cho phép áp dụng khá dễ dàng nhiều tiêu chí. Cách tiếp cận chung như sau: trước tiên, bạn kiểm tra từng điều kiện riêng lẻ, tạo ra các mảng tra cứu tạm thời có giá trị TRUE và FALSE. Tiếp theo, nhân các phần tử của các mảng này để tạo thành một mảng tra cứu thống nhất, trong đó 1 biểu thị kết quả khớp và 0 biểu thị không khớp. Sau đó, bạn đặt lookup_value thành 1 để truy xuất kết quả tìm thấy đầu tiên.
Ví dụ: để lấy ngày của một bài kiểm tra nhất định (tiêu chí 1 – F5) cho một học sinh cụ thể (tiêu chí 2 – F4), các công thức sẽ như sau:
=INDEX(A4:A24, MATCH(1, (B4:B24=F4) * (C4:C24=F5), 0))
=XLOOKUP(1, (B4:B24 = F4) * (C4:C24 = F5), A4:A24)
Trong Excel 2019 và các phiên bản cũ hơn không hỗ trợ mảng động, bạn cần nhập INDEX MATCH dưới dạng công thức mảng bằng cách nhấn nút Ctrl + Shift + Enter các phím với nhau. Nếu không, nó sẽ không hoạt động chính xác. Công thức INDEX MATCH không phải mảng với nhiều điều kiện cũng tồn tại nhưng có cú pháp phức tạp hơn một chút.
Với XLOOKUP, việc lấy thông tin từ nhiều cột lân cận thật dễ dàng, miễn là các cột đó liền kề nhau. Chỉ cần chọn phạm vi bao gồm tất cả các cột bạn cần và dùng nó làm mảng trả về.
Ví dụ: đây là cách bạn có thể truy xuất kết quả bài kiểm tra từ cột B, C và D của một học sinh nào đó trong ô G4:
=XLOOKUP(G4, A4:A25, B4:D25)
Bộ đôi INDEX MATCH tiến xa hơn một chút bằng cách lấy dữ liệu từ bất kỳ cột nào, ngay cả những cột không nằm cạnh nhau:
=INDEX(B4:D25, MATCH(G4, A4:A25, 0), MATCH(F8:G8, B3:D3, 0))
Nói một cách đơn giản, công thức này sử dụng cách tiếp cận INDEX MATCH 2 chiều. MATCH đầu tiên tìm ra số hàng phù hợp cho tên học sinh trong G4 và MATCH thứ hai tìm ra số cột cho các chủ đề trong F8:G8.
Lưu ý rằng trong Excel 2019 trở lên, điều này chỉ hoạt động dưới dạng công thức mảng CSE (yêu cầu nhấn Ctrl + Shift + Enter để tính toán chính xác).
Khi không tìm thấy giá trị đã chỉ định, dạng cơ bản của công thức INDEX và MATCH sẽ trả về lỗi #N/A. Tuy nhiên, bạn có thể giảm thiểu vấn đề này bằng cách gói công thức vào hàm IFERROR hoặc IFNA và cung cấp thông báo tùy chỉnh.
Ví dụ: để thay thế lỗi #N/A bằng văn bản của chính bạn, bạn có thể sử dụng INDEX MATCH cùng với IFNA:
=IFNA(INDEX(B4:B25, MATCH(E4, A4:A25, 0)), "Not found")
Ngoài ra, hãy đưa thông điệp của bạn vào đối số thứ 4 của XLOOKUP như thế này:
=XLOOKUP(E4, A4:A25, B4:B25, "Not found")
Cả hai công thức đều đạt được kết quả như nhau, cho phép bạn xử lý các giá trị bị thiếu một cách khéo léo.
Làm cách nào để chọn giữa XLOOKUP và INDEX MATCH trong Excel? Việc xác định phương pháp phù hợp hơn dựa trên một số yếu tố, chẳng hạn như:
Phiên bản excel. Nếu bạn có Excel 2021 hoặc Office 365, bạn có thể sử dụng XLOOKUP. Nếu không, bạn phải dựa vào INDEX MATCH.
Công thức phức tạp. XLOOKUP có cấu trúc đơn giản hơn với ít đối số hơn. INDEX MATCH Liên quan đến nhiều đối số hơn do sự kết hợp của hai hàm riêng biệt.
Hiệu suất công thức. XLOOKUP có thể hoạt động nhanh hơn INDEX MATCH trong một số trường hợp, đặc biệt nếu bạn sử dụng chế độ tìm kiếm nhị phân. Tuy nhiên, INDEX MATCH cũng có thể được tối ưu hóa bằng cách sử dụng bảng Excel hoặc mảng động.
Công thức linh hoạt. Cả hai công thức đều linh hoạt, có khả năng xử lý nhiều tình huống tra cứu khác nhau. Tuy nhiên, các điều chỉnh có thể khác nhau tùy theo nhiệm vụ cụ thể.
Khả năng đọc công thức. XLOOKUP có cú pháp trực quan hơn và có thể dễ hiểu và gỡ lỗi hơn. INDEX MATCH có cú pháp phức tạp hơn và khó theo dõi cũng như khó khắc phục sự cố hơn.
Vì vậy, cái nào tốt hơn? Nó phụ thuộc vào những gì bạn muốn đạt được và cách bạn thích làm việc. XLOOKUP là một tùy chọn hiện đại với nhiều tính năng hữu ích, khiến nó trở thành lựa chọn tốt cho nhiều tình huống. Tuy nhiên, INDEX MATCH là một giải pháp lâu dài đã được sử dụng trong hàng thập kỷ trong hàng triệu bảng tính và một số người dùng có thể thích giải pháp này vì tính quen thuộc hoặc khả năng kiểm soát bổ sung đối với các chức năng tra cứu.
Đây là câu hỏi thường xuyên của những người dùng Excel nhằm tối ưu hóa công thức của họ và giảm thời gian tính toán. Không có câu trả lời đơn giản vì nó phụ thuộc vào một số biến số như lượng dữ liệu, độ phức tạp của tiêu chí và phiên bản Excel bạn đang sử dụng.
Nói chung, XLOOKUP nhanh hơn INDEX MATCH đối với các tra cứu đơn giản, nhưng INDEX MATCH có thể nhanh hơn đối với các trường hợp nâng cao hơn. Dưới đây là những cân nhắc chính để hướng dẫn sự lựa chọn của bạn:
Sử dụng XLOOKUP nếu:
Bạn đang làm việc với Excel 365 hoặc 2021.
Bạn chỉ cần tra cứu một giá trị dựa trên một tiêu chí trong tập dữ liệu tương đối nhỏ.
Nếu bạn đang làm việc với dữ liệu được sắp xếp trong đó tìm kiếm nhị phân có thể áp dụng được.
Sử dụng INDEX MATCH nếu:
Bạn đang sử dụng Excel 2019 trở lên.
Bạn đang xử lý một tập dữ liệu đáng kể.
Bạn cần thực hiện tra cứu nhiều giá trị dựa trên nhiều tiêu chí.
Một trong những câu hỏi phổ biến nhất thường nảy sinh trong các cuộc thảo luận về Excel là liệu hàm XLOOKUP hiện đại có thể thay thế hiệu quả tổ hợp INDEX MATCH lâu đời hay không. Mặc dù cả hai phương pháp đều có những ưu điểm và hạn chế riêng biệt nhưng quyết định không phải là một quyết định rõ ràng.
Những lợi ích chính của XLOOKUP là:
Cách viết này đơn giản và ngắn gọn hơn INDEX MATCH.
Nó có thể thực hiện tra cứu theo bất kỳ hướng nào: từ trên xuống dưới, từ dưới lên trên, từ trái sang phải cũng như từ phải sang trái.
Nó có thể thực hiện cả kết quả khớp chính xác và gần đúng trong bất kỳ tập dữ liệu nào, trong khi INDEX MATCH bị giới hạn ở các kết quả khớp gần đúng trong dữ liệu được sắp xếp.
Nó có thể xử lý các mảng động và chuyển kết quả sang nhiều ô.
Nó có thể xử lý các lỗi do thiếu giá trị.
Một số nhược điểm của XLOOKUP là:
Nó không tương thích với các phiên bản Excel 2019 cũ hơn trở xuống.
Nó có thể không linh hoạt như INDEX MATCH trong một số trường hợp, chẳng hạn như trả về giá trị từ các cột không liền kề.
Nó có thể không hoạt động tốt với các tập dữ liệu lớn hoặc các công thức không ổn định.
Về bản chất, XLOOKUP thực sự có thể thay thế INDEX MATCH trong nhiều trường hợp, nhưng không phải tất cả. Lựa chọn tùy thuộc vào khả năng tương thích của phiên bản Excel, độ phức tạp của dữ liệu và sở thích cá nhân của bạn.
Bảng bên dưới cung cấp bản tóm tắt ngắn gọn về sự khác nhau của XLOOKUP và INDEX MATCH về chức năng, lợi ích và hạn chế của chúng.
|
Tính năng |
XLOOKUP |
INDEX MATCH |
|
khả dụng |
Excel 2021, Excel 365, Excel cho web |
Tất cả các phiên bản |
|
Kết hợp chuẩn xác |
Đúng |
Đúng |
|
Kết hợp gần đúng |
Đúng |
INDEX MATCH – chỉ trên dữ liệu được sắp xếp INDEX XMATCH – trên dữ liệu chưa được sắp xếp |
|
Tìm kiếm ký tự đại diện |
Đúng |
Đúng |
|
Tìm kiếm nhị phân |
Đúng |
INDEX MATCH – không CHỈ SỐ XMATCH – có |
|
Cột tra cứu cần được sắp xếp |
Để tìm kiếm nhị phân |
INDEX MATCH - cho kết quả khớp gần đúng INDEX XMATCH - để khớp nhị phân |
|
Tra cứu dọc |
Đúng |
Đúng |
|
Tra cứu ngang |
Đúng |
Đúng |
|
Tra cứu bên trái |
Đúng |
Đúng |
|
Trả về toàn bộ hàng hoặc cột |
Đúng |
Đúng |
|
Tìm kiếm theo thứ tự ngược lại |
Đúng |
INDEX MATCH – không CHỈ SỐ XMATCH – có |
|
Nhiều tiêu chí |
Đúng |
Đúng |
|
Trả về giá trị từ nhiều cột |
Có (cột liền kề) |
Có (bất kỳ cột nào) |
|
Xử lý lỗi tích hợp |
Đúng |
KHÔNG |
Tóm lại, cả XLOOKUP và INDEX MATCH đều là những hàm mạnh mẽ và tiện lợi để thực hiện các tác vụ tra cứu khác nhau trong Excel. Tùy thuộc vào tính chất công việc của bạn, bạn có thể thích cái này hơn cái kia. Tuy nhiên, việc biết cả hai phương pháp và cách sử dụng chúng một cách hiệu quả luôn là điều tốt.
