XLOOKUP và VLOOKUP trong Excel: Sự khác biệt và ưu điểm

Trong bài viết này, chúng ta sẽ khám phá sự khác biệt giữa XLOOKUP, VLOOKUP và HLOOKUP, giúp bạn chọn hàm phù hợp cho nhiệm vụ phân tích dữ liệu của mình.

VLOOKUP và XLOOKUP - so sánh cú pháp

Giống như nền tảng của hóa học là bảng tuần hoàn các nguyên tố, trung tâm của bất kỳ hàm Excel nào là tập hợp các tham số của nó. Chúng ta hãy xem xét kỹ hơn các đối số của hai hàm tra cứu phổ biến để làm sáng tỏ các sắc thái của chúng.

Cú pháp của hàm VLOOKUP như sau:

VLOOKUP(giá trị tra cứu, mảng_bảng, col_index_num, [dải_lookup])

Chú thích:

• lookup_value - giá trị cần tìm kiếm.

• table_array - toàn bộ tập dữ liệu.

• col_index_num - số cột để lấy giá trị.

• range_lookup [tùy chọn] - chỉ định xem tìm kết quả khớp gần đúng (mặc định - TRUE) hay kết quả khớp chính xác (FALSE).

Và hàm XLOOKUP có cấu trúc phức tạp hơn một chút:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Chú thích:

• lookup_value - giá trị cần tìm kiếm.

• lookup_array - phạm vi nơi tìm kiếm giá trị tra cứu.

• return_array - phạm vi từ đó trả về một giá trị.

• if_not_found [tùy chọn] - giá trị trả về nếu không tìm thấy kết quả khớp.

• match_mode [tùy chọn] - kiểm soát loại đối sánh như chính xác (0 - mặc định), đối sánh chính xác hoặc nhỏ hơn tiếp theo (-1), đối sánh chính xác hoặc lớn hơn tiếp theo (1), ký tự đại diện (2).

• search_mode [tùy chọn] - chỉ định loại tìm kiếm như từ đầu đến cuối (1 - mặc định), từ cuối đến đầu (-1), tìm kiếm nhị phân tăng dần (2), tìm kiếm nhị phân giảm dần (-2).

Như bạn có thể thấy, cả hai hàm đều cần ít nhất ba tham số để thực hiện công việc của chúng. Nhưng XLOOKUP cung cấp nhiều tùy chọn bổ sung hơn, làm cho nó mạnh mẽ và linh hoạt hơn so với đối tác VLOOKUP. Hãy chia nhỏ những khác biệt chính về cú pháp trong cách chúng hoạt động.

1. Nơi tìm kiếm (tra cứu mảng so với mảng bảng)

VLOOKUP buộc người dùng phải chỉ ra toàn bộ table_array , tìm kiếm giá trị tra cứu chỉ ở cột ngoài cùng bên trái. Cột để trả về giá trị được xác định bởi số cột trong đối số tiếp theo.

Ngược lại, XLOOKUP cho phép các cột tra cứu và trả về được chỉ định riêng. Sự phân tách này đảm bảo tính linh hoạt đặc biệt - cột tra cứu có thể được đặt ở bên trái hoặc bên phải cột giá trị trả về.

2. Nơi trả về một giá trị (trả về mảng so với số cột)

VLOOKUP sử dụng số chỉ mục được mã hóa cứng để biểu thị cột trả về, tạo ra lỗ hổng khi thay đổi cấu trúc mảng tra cứu. Những thay đổi như thêm hoặc xóa cột có thể dễ dàng phá vỡ công thức của bạn.

Với XLOOKUP, bạn cung cấp tham chiếu phạm vi cho mảng trả về, giúp xử lý các thay đổi trong dữ liệu nguồn của bạn tốt hơn. Hơn nữa, XLOOKUP có thể chứa nhiều cột trong mảng trả về, một tính năng yêu cầu hai hoặc nhiều công thức VLOOKUP khác nhau để đạt được.

3. Cách so khớp (chế độ so khớp và tra cứu phạm vi)

Đối số range_lookup trong VLOOKUP và match_mode trong XLOOKUP xác định xem công thức tìm kiếm kết quả khớp chính xác hay gần đúng. Mặc dù cả hai chức năng đều có chung khả năng, nhưng XLOOKUP mặc định khớp chính xác, cung cấp cài đặt mặc định trực quan hơn. Ngoài ra, XLOOKUP cung cấp tùy chọn bổ sung cho kết quả khớp gần đúng trả về giá trị lớn hơn tiếp theo mà không yêu cầu sắp xếp mảng tra cứu, như VLOOKUP thực hiện.

Loại so khớp	VLOOKUP	XLOOKUP
Chính xác	SAI hoặc 0	0 hoặc bị bỏ qua (mặc định)
Gần đúng (chính xác hoặc nhỏ hơn tiếp theo)	TRUE hoặc 1 (mặc định) Cần cột tra cứu được sắp xếp theo thứ tự tăng dần.	-1
Gần đúng (chính xác hoặc lớn hơn tiếp theo)	không áp dụng	1
Ký tự đại diện	SAI (khớp chính xác)	2

4. Cách xử lý giá trị bị thiếu (nếu không tìm thấy)

Trong trường hợp không tìm thấy giá trị tra cứu, VLOOKUP chỉ có thể trả về #N/A! có thể khiến bạn đặt câu hỏi về tính chính xác của công thức.

Bằng cách cung cấp đối số if_not_found , XLOOKUP cho phép bạn trả về thông báo được cá nhân hóa, chẳng hạn như "Không tìm thấy giá trị bạn đang tìm kiếm trong cơ sở dữ liệu".

Nói một cách đơn giản, trong khi VLOOKUP chỉ có thể nói "Tôi không thể tìm thấy nó" khi có lỗi thì XLOOKUP cho phép bạn hiển thị nội dung nào đó cụ thể nếu nó không thể tìm thấy thứ bạn đang tìm kiếm. Ví dụ:

=XLOOKUP(F10, B4:B25, C4:C25, "Oops, nothing is found :(")

5. Cách tìm kiếm (chế độ tìm kiếm)

VLOOKUP luôn bắt đầu tìm kiếm ở trên cùng và dừng khi tìm thấy kết quả phù hợp đầu tiên.

XLOOKUP giới thiệu đối số search_mode , cho phép người dùng thực hiện cả tìm kiếm từ đầu đến cuối và từ cuối đến đầu tiên. Ngoài ra, nó cho phép bạn tận dụng các phương pháp tìm kiếm nhị phân để có kết quả nhanh hơn trong các bộ dữ liệu mở rộng.

Ví dụ: trong bảng bên dưới, VLOOKUP chỉ có thể tìm nhà cung cấp cho đơn hàng đầu tiên của một mặt hàng nhất định, trong khi XLOOKUP có thể làm điều đó cho cả đơn hàng đầu tiên và đơn hàng cuối cùng. Chỉ cần đặt search_mode thành 1 cho trận đấu đầu tiên và -1 cho trận đấu cuối cùng:

=XLOOKUP(J4, F4:F25, G4:G25, , , -1)

Tóm lại, XLOOKUP cung cấp nhiều lựa chọn và kiểm soát hơn cho phép chỉ định các mảng khác nhau để tra cứu và trả về giá trị, cũng như các tùy chọn khác nhau cho chế độ khớp, chế độ tìm kiếm và xử lý lỗi.

Sự khác biệt giữa VLOOKUP và XLOOKUP

Sự khác biệt trong cú pháp VLOOKUP và XLOOKUP đóng một vai trò quan trọng trong hiệu suất của chúng. Bây giờ, hãy khám phá những khác biệt này một cách chi tiết hơn.

1. Vị trí cột tra cứu (tra cứu bên trái)

Trong khi VLOOKUP bị giới hạn chỉ tìm kiếm trong cột đầu tiên trong table_array được chỉ định , XLOOKUP có thể tra cứu các giá trị trong bất kỳ cột nào, không chỉ cột ngoài cùng bên trái. Điều này có nghĩa là XLOOKUP có thể dễ dàng thực hiện tra cứu hai chiều mà không cần sắp xếp lại dữ liệu.

Như bạn có thể nhận thấy trong ảnh chụp màn hình bên dưới, VLOOKUP không mang lại giá trị từ một cột ( Item ) khi nó ở bên phải cột tra cứu ( Nhà cung cấp ).

=VLOOKUP(E4, A4:B25, 1, FALSE)

Mặt khác, XLOOKUP xử lý việc tra cứu bên trái này một cách dễ dàng:

=XLOOKUP(E4, B4:B25, A4:A25)

2. Trả về giá trị từ nhiều cột

VLOOKUP chỉ có thể trả về một giá trị tại một thời điểm, điều này làm hạn chế tính hiệu quả của nó. Ngược lại, XLOOKUP có thể trả về giá trị từ nhiều cột trong một lần. Điều này có nghĩa là bạn có thể sử dụng một công thức XLOOKUP thay vì nhiều VLOOKUP.

Ví dụ: nếu bạn muốn truy xuất tất cả thông tin chi tiết về một mục cụ thể, việc sử dụng VLOOKUP yêu cầu bốn công thức riêng biệt. Bạn sẽ cung cấp các giá trị col_index_num trong khoảng từ 2 (đối với Khu vực trong cột B) đến 5 (đối với Nhà cung cấp trong cột E):

Để có được khu vực:

=VLOOKUP($B$17, $A$4:$E$13, 2, FALSE)

Để được giảm giá:

=VLOOKUP($B$17, $A$4:$E$13, 3, FALSE)

Do hỗ trợ mảng động, XLOOKUP đơn giản hóa quy trình này, cho phép bạn tìm nạp tất cả dữ liệu chỉ bằng một công thức:

=XLOOKUP(B17, A4:A13, B4:E13)

3. Tìm kiếm với nhiều tiêu chí

XLOOKUP có khả năng tìm kiếm giá trị bằng nhiều tiêu chí mà không cần thêm cột trợ giúp, không giống như VLOOKUP.

4. Tìm lần xuất hiện cuối cùng

Để xác định lần xuất hiện cuối cùng bằng cách sử dụng VLOOKUP, bạn cần đảo ngược thứ tự dữ liệu nguồn của mình. Tuy nhiên, với tham số search_mode, XLOOKUP có thể lấy phiên bản cuối cùng của giá trị tra cứu mà không cần di chuyển thêm.

5. Chèn hoặc xóa cột

VLOOKUP dựa vào số cột chỉ mục tĩnh, khiến nó dễ gặp sự cố khi cập nhật nguồn dữ liệu hoặc thực hiện các thay đổi đối với thứ tự cột. XLOOKUP xử lý những sửa đổi đó một cách dễ dàng, đảm bảo công thức vẫn nguyên vẹn.

6. Giới hạn kích thước cho giá trị tra cứu

Trong VLOOKUP, độ dài của tiêu chí tra cứu không được vượt quá 255 ký tự, nếu không bạn sẽ nhận được #VALUE ! lỗi. Nhưng XLOOKUP không áp đặt giới hạn nào về kích thước của giá trị tra cứu. Vì vậy, nếu tập dữ liệu của bạn chứa các chuỗi thực sự dài thì XLOOKUP là giải pháp để tránh lỗi.

7. Sắp xếp phạm vi tra cứu

Mặc dù VLOOKUP yêu cầu sắp xếp theo thứ tự tăng dần cho tìm kiếm gần đúng của nó, nhưng XLOOKUP không có giới hạn này, cung cấp khả năng thích ứng trong các trường hợp không thể sắp xếp.

8. Sự linh hoạt trong việc kết hợp

XLOOKUP nổi bật nhờ tính linh hoạt trong các tùy chọn đối sánh cung cấp nhiều lựa chọn khác nhau, chẳng hạn như đối sánh chính xác, hai loại đối sánh gần đúng (nhỏ hơn tiếp theo hoặc lớn hơn tiếp theo), đối sánh ký tự đại diện và thậm chí cả tìm kiếm nhị phân. Ngược lại, VLOOKUP có ít tùy chọn hơn, giới hạn ở các kết quả khớp chính xác và nhỏ hơn tiếp theo trong các tìm kiếm gần đúng.

9. Xử lý lỗi

XLOOKUP xử lý lỗi hoặc thiếu giá trị tốt hơn. Bạn có thể quyết định nó sẽ hiển thị cái gì nếu nó không thể tìm thấy giá trị được yêu cầu, giá trị mà VLOOKUP không cho phép.

10. Hiệu quả và tốc độ

XLOOKUP nhanh hơn và hiệu quả hơn vì nó chỉ tìm ở nơi cần thiết (mảng tra cứu và mảng trả về) mà không lãng phí thời gian và tài nguyên khi quét toàn bộ bảng như VLOOKUP.

11. Dễ sử dụng

Mặc dù VLOOKUP có ít đối số hơn nhưng tính đơn giản trong cú pháp không nhất thiết đồng nghĩa với việc dễ sử dụng. Các tham số của XLOOKUP, chẳng hạn như mảng tra cứu và mảng trả về, cung cấp cách tiếp cận trực quan hơn so với mảng bảng và số chỉ mục cột của VLOOKUP. Điều này làm cho XLOOKUP thân thiện hơn với người dùng, đặc biệt là đối với người mới bắt đầu.

12. Khả năng tương thích với các phiên bản Excel

Đây là lĩnh vực duy nhất mà VLOOKUP vượt trội hơn đối tác của nó :) Trong khi VLOOKUP hoạt động trong mọi phiên bản Excel, XLOOKUP chỉ sẵn dùng trong Microsoft 365, Excel 2021 và Excel dành cho Web.

Những khác biệt này làm nổi bật tính năng động và hiệu quả của XLOOKUP, khiến nó trở thành một lựa chọn mạnh mẽ thay vì VLOOKUP cho các tác vụ tra cứu dữ liệu khác nhau trong Excel.

XLOOKUP so với VLOOKUP - bảng tóm tắt

Bảng bên dưới nêu ra những khác biệt chính giữa hàm XLOOKUP và VLOOKUP trong Excel. Đây là hướng dẫn nhanh để giúp bạn hiểu cái nào có thể tốt hơn cho nhiệm vụ của bạn.

Tính năng	VLOOKUP	XLOOKUP
Khả dụng	Tất cả các phiên bản	Excel 2021, Excel 365, Excel cho web
Kết hợp chuẩn xác	Đúng	Đúng
Kết hợp gần đúng	1 tùy chọn (nhỏ hơn tiếp theo)	2 tùy chọn (nhỏ hơn tiếp theo hoặc lớn hơn tiếp theo)
Mặc định	Trận đấu gần đúng	Kết hợp chuẩn xác
Tìm kiếm ký tự đại diện	Đúng	Đúng
Tìm kiếm nhị phân	KHÔNG	Đúng
Cột tra cứu cần được sắp xếp	Đối với kết quả gần đúng	Để tìm kiếm nhị phân
Giới hạn cho giá trị tra cứu	255 ký tự	KHÔNG
Tra cứu bên trái	KHÔNG	Đúng
Tra cứu ngang (theo hàng)	KHÔNG	Đúng
Tìm kiếm theo thứ tự ngược lại	KHÔNG	Đúng
Nhiều tiêu chí	Phức tạp, có cột trợ giúp	Dễ dàng hơn, không cần cột trợ giúp
Trả về giá trị từ nhiều cột	KHÔNG	Đúng
Xử lý lỗi tích hợp	KHÔNG	Đúng
Thêm/bớt cột có thể làm hỏng công thức	Đúng	KHÔNG

XLOOKUP so với HLOOKUP

Trong các phiên bản Excel cũ hơn, nếu bạn muốn tra cứu dữ liệu theo cột, bạn sẽ sử dụng VLOOKUP và đối với các hàng, hãy sử dụng HLOOKUP. Tuy nhiên, hàm XLOOKUP có thể thực hiện cả hai công việc! Hãy xem nó so sánh với HLOOKUP như thế nào.

1. Tìm kiếm ở hàng bất kỳ

HLOOKUP chỉ có thể tìm kiếm ở hàng trên cùng của table_array , trong khi XLOOKUP có thể tìm kiếm ở bất kỳ hàng nào.

2. Tìm kiếm theo cả hai hướng

HLOOKUP chỉ có thể tìm kiếm từ trái sang phải nhưng XLOOKUP có thể tìm kiếm theo cả hai hướng. Nó giống như có một công cụ tìm kiếm hai mặt.

3. Kết hợp các loại và mặc định

HLOOKUP mặc định là khớp gần đúng và cần sắp xếp hàng tra cứu tăng dần. Mặt khác, XLOOKUP mặc định khớp chính xác, đây là điều bạn thường cần trong hầu hết các trường hợp.

Tất cả những khác biệt khác mà chúng ta đã nói đến giữa XLOOKUP và VLOOKUP cũng áp dụng cho HLOOKUP. Vì vậy, XLOOKUP giống như một siêu hàm trong Excel có thể tìm và trả về các giá trị từ bất kỳ bảng hoặc phạm vi nào với tính linh hoạt, khả năng kiểm soát và tính năng cao hơn cả hai hàm tra cứu cũ cộng lại.