VietNetwork.Vn

Là một người đam mê Excel lâu năm, tôi luôn thích sử dụng VLOOKUP, một trong những hàm tra cứu nổi tiếng nhất của Excel. Tuy nhiên, việc Microsoft giới thiệu XLOOKUP vào năm 2019 đã thay đổi mọi thứ. Khi nhận ra XLOOKUP hữu ích như thế nào, tôi biết mình sẽ không bao giờ hối hận.

Tôi sẽ sử dụng tham chiếu ô trực tiếp để minh họa cho các điểm của mình trong bài viết này, vì chúng rõ ràng hơn tham chiếu có cấu trúc. Tôi cũng sẽ không nói rõ về HLOOKUP vì—ngoại trừ hướng của nó—nó hoạt động theo cùng một cách chính xác như VLOOKUP.


1. Cú pháp: XLOOKUP và VLOOKUP

Trước khi giải thích lý do tại sao tôi thích XLOOKUP hơn VLOOKUP, tôi sẽ cho bạn thấy chúng hoạt động như thế nào.

1.1. XLOOKUP

XLOOKUP có sáu đối số:

=XLOOKUP(a,b,c,d,e,f)
Trong đó:

    a (bắt buộc) là giá trị tra cứu,
    b (bắt buộc) là mảng tra cứu,
    c (bắt buộc) là mảng trả về,
    d (tùy chọn) là văn bản trả về nếu giá trị tra cứu ( a ) không được tìm thấy trong mảng tra cứu ( b ),
    e (tùy chọn) là chế độ khớp lệnh và
    f (tùy chọn) là chế độ tìm kiếm.

Trong ví dụ này, tôi muốn Excel tra cứu ID nhân viên dựa trên tên trong ô H1 và trả kết quả về ô H2.


Để thực hiện việc này, tôi sẽ nhập công thức sau vào ô H2:

=XLOOKUP(H1,B2:B12,A2:A12,"Invalid name",0,1)
Trong trường hợp này, ô H1 chứa giá trị mà Excel cần tra cứu (Mary), B2 đến B12 là nơi có thể tìm thấy giá trị đó (tên nhân viên), A2 đến A12 là nơi sẽ lấy kết quả tương ứng (ID nhân viên) và "Tên không hợp lệ" là nội dung tôi muốn Excel trả về nếu không tìm thấy giá trị tra cứu trong mảng tra cứu.

Tôi đã chọn các giá trị mặc định cho đối số e và f vì tôi muốn có sự khớp chính xác và muốn Excel tìm kiếm từ đầu mảng tra cứu (thông tin chi tiết hơn sẽ được đề cập sau).


1.2. VLOOKUP

Sau đây là cú pháp VLOOKUP có bốn đối số:

=VLOOKUP(a,b,c,d)
Trong đó:

    a (bắt buộc) là giá trị tra cứu,
    b (bắt buộc) là mảng tìm kiếm và trả về,
    c (bắt buộc) là số chỉ mục cột và
    d (tùy chọn) là chế độ khớp.

Trong ví dụ này, tôi muốn Excel tra cứu quốc tịch dựa trên ID nhân viên trong ô H4 và trả kết quả về ô H5.


Để thực hiện điều này, trong ô H5, tôi sẽ nhập

=VLOOKUP(H4,A2:E12,5,FALSE)
vì ô H4 chứa giá trị tra cứu (ID 3264), nên các ô từ A2 đến E12 là nơi Excel cần tìm giá trị đó và giá trị trả về tương ứng, cột thứ năm (quốc gia) là mảng trong đó kết quả sẽ được tìm thấy và tôi muốn một kết quả khớp chính xác (SAI).

2. Không cần đếm cột

Một điểm khác biệt chính giữa VLOOKUP và XLOOKUP là hàm trước yêu cầu tôi chỉ định số chỉ mục cột nơi kết quả sẽ được tìm thấy, trong khi hàm sau thì không. Điều này là do VLOOKUP kết hợp các mảng tra cứu và trả về thành một đối số, trong khi XLOOKUP định nghĩa chúng trong hai đối số riêng biệt.

2.1. VLOOKUP

Việc phải chỉ định số chỉ mục cột trong VLOOKUP có thể dẫn đến một số vấn đề:

    Rất dễ vô tình đếm nhầm số cột, đặc biệt nếu mảng tra cứu của bạn có hàng trăm cột.
    Việc thêm hoặc xóa cột có thể ảnh hưởng đến độ chính xác của số chỉ mục cột.
    Mảng tra cứu phải là cột ngoài cùng bên trái và mảng trả về phải ở bên phải. Điều này hạn chế tính linh hoạt của VLOOKUP.

2.2. XLOOKUP

Mặt khác, XLOOKUP chứa mảng tra cứu dưới dạng một đối số riêng biệt, nghĩa là bạn có thể tận dụng những lợi ích sau:

    Không cần đếm! Chỉ cần sử dụng chuột để chọn mảng trả về khi bạn đến phần đó của công thức. Điều này giúp tiết kiệm thời gian và tăng độ chính xác.
    Vì mảng trả về nằm trong phạm vi ô được chỉ định nên việc xóa hoặc thêm cột vào bảng tính sẽ không ảnh hưởng đến công thức XLOOKUP của bạn.
    Mảng trả về có thể nằm ở cả hai bên của mảng tra cứu, nghĩa là XLOOKUP linh hoạt hơn VLOOKUP.

3. Thêm nhiều tùy chọn khớp gần đúng

Cả VLOOKUP và XLOOKUP đều có thể trả về kết quả khớp chính xác (giá trị tương ứng chính xác trong một hàng) và kết quả khớp gần đúng (giá trị tương ứng gần đó).

3.1. VLOOKUP

Cụ thể hơn, phép khớp gần đúng của VLOOKUP (được biểu thị bằng TRUE trong công thức) tìm kiếm xuống mảng tra cứu cho đến khi tìm thấy giá trị lớn hơn giá trị tra cứu. Sau đó, nó trả về giá trị cách đó một hàng.

Lấy điểm 65 của Học sinh D trong ví dụ bên dưới. VLOOKUP lấy giá trị tra cứu là 65, xem lại mảng tra cứu, tìm giá trị đầu tiên lớn hơn giá trị tra cứu (trong trường hợp này là 70), sau đó trả về điểm từ hàng phía trên (điểm C).


Điều này có hai nhược điểm. Đầu tiên, mảng tra cứu phải được liệt kê theo thứ tự tăng dần. Thứ hai, tôi phải thêm một hàng FAIL vào mảng tra cứu vì tôi không có tùy chọn trong VLOOKUP để nêu đối số không khớp.

3.2. XLOOKUP

Mặt khác, XLOOKUP cung cấp ba lựa chọn thay thế cho phép so sánh gần đúng một chiều của VLOOKUP:

    -1: Trả về giá trị nhỏ nhất tiếp theo trong mảng tra cứu nếu không có kết quả khớp chính xác.
    1: Trả về giá trị lớn nhất tiếp theo trong mảng tra cứu nếu không có kết quả khớp chính xác.
    2: Sử dụng ký tự đại diện để cho phép tra cứu linh hoạt hơn.

Một lần nữa, hãy lấy Học sinh D làm ví dụ. Với điểm là 65, Excel sẽ thấy rằng giá trị nhỏ nhất tiếp theo trong mảng tra cứu là 60 và giá trị lớn nhất tiếp theo là 70. Vì học sinh vẫn chưa đạt đến ngưỡng cho điểm B (70), tôi cần Excel lấy giá trị nhỏ nhất tiếp theo trong mảng tra cứu (60) để trả về điểm C từ mảng trả về. Vì vậy, tôi sẽ nhập -1 làm tùy chọn khớp trong công thức.


Điều này có nghĩa là mảng tra cứu không cần phải theo thứ tự—Excel sẽ tìm kiếm toàn bộ mảng tra cứu để tìm giá trị cao hơn hoặc thấp hơn gần nhất nếu không có kết quả khớp chính xác. Tôi cũng có thể bỏ hàng FAIL khỏi mảng tra cứu của mình vì nếu điểm của học sinh không khớp với bất kỳ điểm nào, tôi có thể sử dụng đối số thứ tư trong cú pháp XLOOKUP để trả về từ FAIL.

4. Thêm chế độ tìm kiếm

Trong khi VLOOKUP tìm kiếm từ đầu đến cuối, trả về giá trị khớp đầu tiên, XLOOKUP cung cấp bốn tùy chọn tìm kiếm.

4.1. VLOOKUP

Trong hầu hết các trường hợp tra cứu, tìm kiếm mảng tra cứu từ đầu đến cuối sẽ trả về kết quả bạn cần. Ví dụ, nếu bạn có một danh mục số điện thoại và tên người, sử dụng VLOOKUP để tìm số điện thoại dựa trên tên bạn nhập sẽ hoạt động tốt, vì tên của người đó chỉ có khả năng xuất hiện một lần.

4.2. XLOOKUP

Tuy nhiên, XLOOKUP cho phép bạn chọn hướng tìm kiếm:

    1: Tìm kiếm từ đầu đến cuối
    -1: Tìm kiếm từ cuối đến đầu
    2: Tìm kiếm nhị phân (với mảng tra cứu theo thứ tự tăng dần)
    -2 Tìm kiếm nhị phân (với mảng tra cứu theo thứ tự giảm dần)

Lợi ích của tra cứu từ cuối đến đầu là bạn có thể tìm thấy lần xuất hiện gần đây nhất của một giá trị trong mảng tra cứu được liệt kê theo thứ tự ngày. Để đạt được điều này với VLOOKUP, trước tiên bạn phải đảo ngược thứ tự dữ liệu.

5. Xác định lỗi đầu ra

Một tính năng thực sự hữu ích của XLOOKUP là "đối số nếu không tìm thấy" mà VLOOKUP không có.

5.1. VLOOKUP

Nếu không tìm thấy giá trị trong công thức VLOOKUP khớp chính xác, Excel sẽ trả về thông báo lỗi #N/A đáng sợ. Để khắc phục lỗi này, tôi luôn nhúng các hàm VLOOKUP trong hàm IFERROR để có thể xác định đầu ra nếu VLOOKUP không tìm thấy kết quả khớp.

=IFERROR(VLOOKUP(B6,$E$2:$F$8,2,TRUE)," ")
Mặc dù đây là giải pháp hợp lý, nhưng nó làm cho việc viết công thức trở nên phức tạp hơn nhiều và có thể ẩn chứa những vấn đề có thể ảnh hưởng đến độ chính xác của phân tích dữ liệu.

5.2. XLOOKUP

Vì XLOOKUP có sẵn đối số "nếu không tìm thấy", bạn có thể xác định điều gì sẽ xảy ra nếu giá trị không xuất hiện trong tra cứu, giúp bạn không phải nhúng công thức vào trong IFERROR.

6. Trả về một mảng tràn

Một trong những thuộc tính cứng đầu nhất của hàm VLOOKUP là nó chỉ có thể trả về một kết quả trùng khớp duy nhất, trong khi XLOOKUP có thể trả về một phạm vi.

6.1. XLOOKUP

Trong ví dụ này, hãy nhập

=XLOOKUP(I1,A2:A7,B2:F7)
tra cứu giá trị trong ô I1 (trong trường hợp này là Quizpicable Me), tìm kiếm giá trị đó trong các ô từ A2 đến A7 và trả về tất cả các giá trị tương ứng dưới dạng một mảng tràn.


6.2. VLOOKUP

Nếu tôi thử sao chép điều này bằng VLOOKUP, tôi sẽ nhập

=VLOOKUP(I1,A2:F7,2:6)
nhưng điều này trả về lỗi #REF! vì đối số thứ ba (số chỉ mục cột) chỉ có thể là một chữ số, không phải là một phạm vi. Điều này có nghĩa là XLOOKUP thích ứng hơn nhiều, vì nó có thể trả về một giá trị duy nhất hoặc một phạm vi, tùy thuộc vào các tham số bạn thêm vào công thức.

Vì các phiên bản Excel trước năm 2019 không hỗ trợ XLOOKUP, đừng loại bỏ hoàn toàn VLOOKUP và HLOOKUP! Vẫn có những trường hợp bạn cần sử dụng chúng, chẳng hạn như khi bạn gửi bảng tính cho người không nâng cấp gói Office của họ trong vài năm.