Note: The other languages of the website are Google-translated. Back to English

Cách sử dụng Hàm XLOOKUP MỚI & NÂNG CAO trong Excel (10 Ví dụ)

Excel mới XLOOKUP là chức năng Tra cứu mạnh mẽ nhất và dễ dàng nhất mà Excel có thể cung cấp. Bằng những nỗ lực không ngừng, Microsoft cuối cùng đã phát hành hàm XLOOKUP này để thay thế hàm VLOOKUP, HLOOKUP, INDEX + MATCH và các hàm tra cứu khác.

Trong hướng dẫn này, chúng tôi sẽ chỉ cho bạn lợi thế của XLOOKUP là gìlàm thế nào bạn có thể lấy nó và áp dụng nó để giải quyết các vấn đề tra cứu khác nhau.

Làm thế nào để tải XLOOKUP?

Cú pháp hàm XLOOKUP

Ví dụ về hàm XLOOKUP

Tải xuống tệp mẫu XLOOKUP

Làm thế nào để tải XLOOKUP?

từ Hàm XLOOKUP is chỉ có sẵn in Excel dành cho Microsoft 365, Excel 2021Excel cho web, bạn có thể nâng cấp Excel của mình lên phiên bản có sẵn để tải XLOOKUP.

Cú pháp hàm XLOOKUP

Hàm XLOOKUP tìm kiếm một dải ô hoặc một mảng và sau đó trả về giá trị của kết quả khớp đầu tiên. Các cú pháp của hàm XLOOKUP như sau:

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

hàm xlookup 1

đối số:

  1. Lookup_value (bắt buộc): giá trị bạn đang tìm kiếm. Nó có thể nằm trong bất kỳ cột nào của phạm vi table_array.
  2. Lookup_array (bắt buộc): mảng hoặc phạm vi nơi bạn tìm kiếm giá trị tra cứu.
  3. Return_array (bắt buộc): mảng hoặc dải ô mà bạn muốn lấy giá trị.
  4. If_not_found (tùy chọn): giá trị trả về khi không tìm thấy kết quả khớp hợp lệ. Bạn có thể tùy chỉnh văn bản trong [if_not_found] để hiển thị không có văn bản nào trùng khớp.
    Nếu không, giá trị trả về sẽ là # N / A theo mặc định.
  5. Match_mode (tùy chọn): ở đây bạn có thể chỉ định cách đối sánh lookup_value với các giá trị trong lookup_array.
    • 0 (mặc định) = Đối sánh chính xác. Nếu không tìm thấy kết quả phù hợp nào, hãy trả về # N / A.
    • -1 = Đối sánh chính xác. Nếu không tìm thấy kết quả phù hợp nào, hãy trả về giá trị nhỏ hơn tiếp theo.
    • 1 = Kết hợp chính xác. Nếu không tìm thấy kết quả phù hợp nào, hãy trả về giá trị lớn hơn tiếp theo.
    • 2 = Kết hợp một phần. Sử dụng các ký tự đại diện như *,? và ~ để chạy đối sánh ký tự đại diện.
  6. Search_mode (tùy chọn): tại đây bạn có thể chỉ định thứ tự tìm kiếm để thực hiện.
    • 1 (default) = Tìm kiếm lookup_value từ mục đầu tiên đến mục cuối cùng trong lookup_array.
    • -1 = Tìm kiếm lookup_value từ mục cuối cùng đến mục đầu tiên. Nó hữu ích khi bạn cần lấy kết quả khớp cuối cùng trong lookup_array.
    • 2 = Tiến hành tìm kiếm nhị phân yêu cầu lookup_array được sắp xếp theo thứ tự tăng dần. Nếu không được sắp xếp, kết quả trả về sẽ không hợp lệ.
    • -2 = Thực hiện tìm kiếm nhị phân yêu cầu lookup_array được sắp xếp theo thứ tự giảm dần. Nếu không được sắp xếp, kết quả trả về sẽ không hợp lệ.

Trong thông tin chi tiết về các đối số của hàm XLOOKUP, vui lòng làm như sau:

1. Nhập cú pháp dưới đây vào một ô trống, xin lưu ý rằng bạn chỉ cần nhập một mặt của dấu ngoặc.

=XLOOKUP(

hàm xlookup 2

2. nhấn Ctrl + A, Sau đó một hộp nhắc nhở bật lên hiển thị đối số của hàm. Và mặt còn lại của khung được hoàn thành tự động.

hàm xlookup 3

3. Kéo bảng dữ liệu xuống, sau đó bạn có thể thấy tất cả sáu đối số hàm của XLOOKUP.

hàm xlookup 4 >>> hàm xlookup 5

Ví dụ về hàm XLOOKUP

Tôi chắc rằng bây giờ bạn đã nắm vững các nguyên tắc cơ bản của hàm XLOOKUP. Hãy đi sâu vào ví dụ thực tế của XLOOKUP.

Ví dụ 1: Đối sánh chính xác

Thực hiện đối sánh chính xác với XLOOKUP

Bạn đã bao giờ cảm thấy bực bội vì phải chỉ định chế độ đối sánh chính xác bất cứ khi nào bạn sử dụng hàm VLOOKUP? May mắn thay, rắc rối này không còn nữa khi bạn thử chức năng XLOOKUP tuyệt vời. Theo mặc định, XLOOKUP tạo một kết hợp chính xác.

Bây giờ, giả sử bạn có một danh sách tồn kho vật tư văn phòng và bạn cần biết giá thống nhất của một mặt hàng, hãy làm như sau.

hàm xlookup 6

công thức dưới đây vào ô trống F2 và nhấn Đi vào phím để nhận kết quả.

=XLOOKUP(E2,A2:A10,C2:C10)

hàm xlookup 7

Bây giờ bạn đã biết đơn giá của Chuột với công thức XLOOKUP nâng cao. Bởi vì mã đối sánh đã được mặc định là đối sánh chính xác, bạn không cần chỉ định. Vì vậy, dễ dàng hơn và hiệu quả hơn nhiều so với VLOOKUP.

Chỉ cần một vài cú nhấp chuột để có được kết quả phù hợp chính xác

Có lẽ bạn đang sử dụng phiên bản Excel thấp hơn và chưa có kế hoạch nâng cấp lên Excel 2021 hoặc Microsoft 365. Trong trường hợp này, tôi sẽ giới thiệu một tính năng tiện dụng - Tìm kiếm một giá trị trong công thức danh sách of Kutools cho Excel. Với tính năng này, bạn có thể nhận được kết quả mà không cần công thức phức tạp hoặc truy cập vào XLOOKUP.

Với our Bổ trợ Excel Viện KHKTHNtất cả, vui lòng làm như sau:

1. Nhấp vào ô để đặt kết quả phù hợp.

2. Đi đến Kutools tab, nhấp vào Công thức trợ giúp, và sau đó nhấp vào Trình trợ giúp Công thức trong danh sách thả xuống.

hàm xlookup 8

3. bên trong Hộp thoại Trình trợ giúp Công thức, vui lòng cấu hình như sau:

  • Chọn Tra cứu trong Phần loại công thức;
  • Trong tạp chí Chọn một phần công thức, lựa chọn Tìm kiếm một giá trị trong danh sách;
  • Trong tạp chí Phần nhập đối số, vui lòng làm như sau:
    • Trong tạp chí Hộp table_array, chọn phạm vi dữ liệu có chứa giá trị tra cứu và giá trị kết quả;
    • Trong tạp chí Hộp lookup_value, chọn ô hoặc phạm vi giá trị bạn đang tìm kiếm. Xin lưu ý rằng nó phải nằm trong cột đầu tiên của table_array;
    • Trong tạp chí Hộp cột, chọn cột mà bạn sẽ trả về giá trị phù hợp.

hàm xlookup 9

4. Nhấp vào OK để nhận kết quả.

hàm xlookup 10

Nhấp để tải xuống Kutools cho Excel để dùng thử miễn phí 30 ngày.


Ví dụ 2. Kết hợp gần đúng

Thực hiện đối sánh gần đúng với XLOOKUP

Để chạy một tra cứu gần đúng, Bạn cần phải đặt chế độ đối sánh thành 1 hoặc -1 trong đối số thứ năm. Khi không tìm thấy kết quả phù hợp chính xác nào, nó sẽ trả về giá trị lớn hơn hoặc nhỏ hơn tiếp theo.

Trong trường hợp này, bạn cần biết thuế suất thu nhập của nhân viên. Ở phía bên trái của bảng tính là Khung thuế thu nhập liên bang cho năm 2021. Làm thế nào bạn có thể nhận được mức thuế của nhân viên của mình trong cột E? Đừng lo lắng. Vui lòng làm như sau:

1. Nhập công thức dưới đây vào ô trống E2, và nhấn Đi vào phím để nhận kết quả.
Sau đó, thay đổi định dạng của kết quả trả về khi bạn cần.

=XLOOKUP(D2,B2:B8,A2:A8,,1)

hàm xlookup 11 >>> hàm xlookup 12

√ Lưu ý: Đối số thứ tư [If_not_found] là tùy chọn, vì vậy tôi chỉ bỏ qua nó.

2. Bây giờ bạn biết thuế suất của ô D2. Để nhận phần còn lại của kết quả, Bạn cần phải chuyển đổi các tham chiếu ô của lookup_array và return_array thành tuyệt đối.

  • Nhấp đúp vào ô E2 để hiển thị công thức = XLOOKUP (D2, B2: B8, A2: A8,, 1);
  • Chọn phạm vi tra cứu B2: B8 trong công thức, nhấn phím F4 để nhận $ B $ 2: $ B $ 8;
  • Chọn phạm vi trả về A2: A8 trong công thức, nhấn phím F4 để nhận $ A $ 2: $ A $ 8;
  • Nhấn Đi vào để lấy kết quả của ô E2.
hàm xlookup 13 >>> hàm xlookup 14

3. Sau đó kéo chốt điền xuống để nhận được tất cả các kết quả.

hàm xlookup 15

√ Lưu ý:

  • Nhấn phím F4 trên bàn phím cho phép bạn thay đổi tham chiếu ô thành tham chiếu tuyệt đối bằng cách thêm ký hiệu đô la trước hàng và cột.
  • Sau khi áp dụng tham chiếu tuyệt đối để tra cứu và trả về dải ô, chúng tôi đã thay đổi công thức trong ô E2 phiên bản này:

=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)

  • Khi bạn kéo chốt điền xuống từ ô E2, các công thức trong mỗi ô của cột E là chỉ thay đổi ở khía cạnh lookup_value.
    Ví dụ, công thức trong E13 bây giờ được chuyển thành sau:

=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)

Ví dụ 3: Đối sánh ký tự đại diện

Thực hiện đối sánh ký tự đại diện với XLOOKUP

Trước khi chúng tôi xem xét Hàm đối sánh ký tự đại diện XLOOKUP, chúng ta hãy xem trước ký tự đại diện là gì.

Trong Microsoft Excel, ký tự đại diện là một loại ký tự đặc biệt có thể thay thế bất kỳ ký tự nào. Nó đặc biệt hữu ích khi bạn muốn thực hiện tra cứu từng phần đối sánh.

Có ba loại ký tự đại diện: dấu hoa thị (*), dấu chấm hỏi (?)dấu ngã (~).

  • Dấu hoa thị (*) đại diện cho bất kỳ số ký tự nào trong văn bản;
  • Dấu hỏi (?) Là viết tắt của bất kỳ ký tự đơn nào trong văn bản;
  • Dấu ngã (~) được sử dụng để biến các ký tự đại diện (*,? ~) Thành các ký tự chữ. Đặt dấu ngã (~) trước các ký tự đại diện để thực hiện chức năng này;

Trong hầu hết các trường hợp, khi chúng tôi thực hiện hàm đối sánh ký tự đại diện XLOOKUP, chúng tôi sử dụng ký tự dấu hoa thị (*). Bây giờ chúng ta hãy xem cách đối sánh ký tự đại diện hoạt động.

Giả sử bạn có danh sách Giá trị vốn hóa thị trường chứng khoán của 50 công ty lớn nhất của Mỹ và bạn muốn biết giá trị vốn hóa thị trường của một vài công ty nhưng tên công ty là viết tắt, đây là kịch bản hoàn hảo cho khớp ký tự đại diện. Hãy làm theo tôi từng bước để thực hiện thủ thuật.

hàm xlookup 16

√ Lưu ý: Để thực hiện đối sánh ký tự đại diện, điều quan trọng nhất là đặt đối số thứ năm [match_mode] thành 2.

1. Nhập công thức dưới đây vào ô trống H3 và nhấn Đi vào phím để nhận kết quả.

=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)

hàm xlookup 17 >>> hàm xlookup 18

2. Bây giờ bạn biết kết quả của ô H3. Để nhận được phần còn lại của kết quả, bạn cần cố định lookup_array và return_array bằng cách đặt con trỏ vào mảng và nhấn phím F4. Khi đó công thức trong H3 trở thành:

=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)

3. Kéo chốt điền xuống để nhận được tất cả các kết quả.

hàm xlookup 19

√ Lưu ý:

  • Giá trị tra cứu của công thức trong ô H3 là "*" & G3 & "*". chúng tôi nối ký tự đại diện dấu hoa thị (*) với giá trị G3 bằng cách sử dụng dấu và (&).
  • Đối số thứ tư [If_not_found] là tùy chọn, vì vậy tôi chỉ bỏ qua nó.
Ví dụ 4: Nhìn bên trái

Nhìn sang bên trái bằng XLOOKUP

Một nhược điểm của hàm VLOOKUP đó là nó bị ràng buộc để thực hiện tra cứu ở bên phải của cột tra cứu. Nếu bạn cố gắng tìm kiếm các giá trị còn lại trong cột tra cứu, bạn sẽ gặp lỗi # N / A. Đừng lo lắng. XLOOKUP là chức năng tra cứu hoàn hảo để giải quyết vấn đề này.

XLOOKUP được thiết kế để tra cứu các giá trị để bên trái hoặc bên phải của cột tra cứu. Nó không có giới hạn và đáp ứng nhu cầu của người dùng Excel. Trong ví dụ dưới đây, chúng tôi sẽ chỉ cho bạn thủ thuật.

Giả sử bạn có danh sách các quốc gia có mã điện thoại và bạn muốn tìm tên quốc gia có mã điện thoại đã biết.

hàm xlookup 20

Chúng ta cần tra cứu cột C và trả về giá trị trong cột A. Vui lòng thực hiện như sau:

1. Nhập công thức dưới đây vào ô trống G2.

=XLOOKUP(F2,C2:C11,A2:A11)

2. Nhấn nút Đi vào phím để nhận kết quả.

hàm xlookup 21

√ Lưu ý: Hàm XLOOKUP nhìn sang trái có thể thay thế Chỉ mục và Khớp để tìm kiếm các giá trị ở bên trái.

Tra cứu giá trị từ phải sang trái với một vài cú nhấp chuột

Đối với những người không muốn nhớ công thức, ở đây, tôi sẽ giới thiệu một tính năng hữu ích - Tra cứu từ phải sang trái of Kutools cho Excel. Với tính năng này, bạn có thể thực hiện tra cứu từ phải sang trái trong vòng vài giây.

Với our Bổ trợ Excel Viện KHKTHNtất cả, vui lòng làm như sau:

1. Đi đến Kutools tab trong Excel, tìm SIÊU NHÌN, Và nhấp vào LOOKUP từ phải sang trái trong danh sách thả xuống.

hàm xlookup 22

2. bên trong Hộp thoại LOOKUP từ Phải sang Trái, bạn cần cấu hình như sau:

  • Trong tạp chí Tra cứu giá trị và phần phạm vi đầu ra, chỉ định phạm vi tra cứuPhạm vi đầu ra;
  • Trong tạp chí Phần phạm vi dữ liệu, đầu vào phạm vi dữ liệu, sau đó chỉ định cột chínhcột trả lại;

hàm xlookup 23

3. Nhấp vào OK để nhận kết quả.

hàm xlookup 24

Nhấp để tải xuống Kutools cho Excel để dùng thử miễn phí 30 ngày.


Ví dụ 5: Tra cứu theo chiều dọc hoặc chiều ngang

Thực hiện tra cứu theo chiều dọc hoặc chiều ngang với XLOOKUP

Là người dùng Excel, bạn có thể quen thuộc với các hàm VLOOKUP và HLOOKUP. VLOOKUP là để tìm theo chiều dọc trong một cộtHLOOKUP là xem theo chiều ngang trong một hàng.

Hiện nay XLOOKUP mới kết hợp cả hai, điều đó có nghĩa là bạn chỉ cần sử dụng một cú pháp để thực hiện tra cứu theo chiều dọc hoặc tra cứu theo chiều ngang. Thiên tài, phải không?

Trong ví dụ dưới đây, chúng tôi sẽ minh họa cách bạn chỉ sử dụng một cú pháp XLOOKUP để chạy tra cứu theo chiều dọc hoặc chiều ngang.

Để thực hiện tra cứu theo chiều dọc, gõ công thức dưới đây trong ô trống E2, nhấn Đi vào phím để nhận kết quả.

=XLOOKUP(E1,A2:A13,B2:B13)

hàm xlookup 25

Để thực hiện tra cứu theo chiều ngang, gõ công thức dưới đây trong ô trống P2, nhấn Đi vào phím để nhận kết quả.

=XLOOKUP(P1,B1:M1,B2:M2)

hàm xlookup 26

Như bạn có thể thấy, cú pháp giống nhau. Các sự khác biệt duy nhất giữa hai công thức là bạn nhập cột trong tra cứu dọc khi bạn nhập hàng trong tra cứu ngang.

Ví dụ 6: Tra cứu hai chiều

Thực hiện tra cứu hai chiều với XLOOKUP

Bạn vẫn đang sử dụng Hàm INDEX và MATCH để tra cứu một giá trị trong phạm vi hai chiều? Cố gắng XLOOKUP được cải tiến hoàn thành công việc của bạn dễ dàng hơn.

XLOOKUP có thể thực hiện tra cứu kép, tìm một ngã tư của hai giá trị. Qua làm tổ một XLOOKUP bên trong XLOOKUP khác, XLOOKUP bên trong có thể trả về toàn bộ hàng hoặc cột, sau đó hàng hoặc cột được trả về này được nhập vào XLOOKUP bên ngoài dưới dạng mảng trả về.

Giả sử bạn có một danh sách điểm của sinh viên các ngành khác nhau, bạn muốn biết điểm môn Hóa của Kim.

hàm xlookup 43

Hãy xem cách chúng tôi sử dụng XLOOKUP kỳ diệu để thực hiện thủ thuật.

    • Chúng tôi chạy XLOOKUP “bên trong” để trả về các giá trị của một cột enter. XLOOKUP (H2, B1: E1, B2: E10) có thể nhận được một loạt các điểm Hóa học.
    • Chúng tôi lồng XLOOKUP “bên trong” bên trong XLOOKUP “bên ngoài” bằng cách sử dụng XLOOKUP “bên trong” như một return_array trong công thức hoàn chỉnh.
    • Sau đó, đây là công thức cuối cùng:

=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))

  • công thức trên vào ô trống H3, nhấn Đi vào để nhận kết quả.

hàm xlookup 27

Hoặc bạn có thể làm cách khác xung quanh, sử dụng XLOOKUP “bên trong” để trả về các giá trị của toàn bộ hàng, là tất cả các điểm của môn học Kim. Sau đó, sử dụng XLOOKUP “bên ngoài” để tìm điểm Hóa học trong số tất cả các điểm môn học của Kim.

    • công thức dưới đây trong ô trống H4, và nhấn Đi vào để nhận kết quả.

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

hàm xlookup 28

Chức năng tra cứu Hai chiều của XLOOKUP cũng là minh họa hoàn hảo cho chức năng tra cứu theo chiều dọc và chiều ngang của nó. Hãy thử nếu bạn muốn!

Ví dụ 7: Tùy chỉnh thông báo không tìm thấy

Tùy chỉnh thông báo không tìm thấy bằng XLOOKUP

Cũng giống như các hàm tra cứu khác, khi hàm XLOOKUP không thể tìm thấy một trận đấu, Các # N / A thông báo lỗi sẽ được trả lại. Nó có thể gây nhầm lẫn cho một số người dùng Excel. Nhưng tin tốt là xử lý lỗi có sẵn trong đối số thứ tư của hàm XLOOKUP.

Với đối số [if_not_found] tích hợp sẵn, bạn có thể chỉ định một thông báo tùy chỉnh để thay thế kết quả # N / A. Nhập văn bản bạn cần vào đối số thứ tư tùy chọn và gửi văn bản vào dấu ngoặc kép (").

Ví dụ: không tìm thấy thành phố Denver, vì vậy XLOOKUP trả về thông báo lỗi # N / A. Nhưng sau khi chúng tôi tùy chỉnh đối số thứ tư với văn bản “Không khớp”, công thức sẽ hiển thị văn bản “Không khớp” thay vì thông báo lỗi.

công thức dưới đây trong ô trống F3, và nhấn Đi vào để nhận kết quả.

=XLOOKUP(E2,A2:A11,C2:C11,"No Match")

hàm xlookup 29

Tùy chỉnh lỗi # N / A bằng một tính năng tiện dụng

Để nhanh chóng ghi đè Lỗi # N / A với thông báo tùy chỉnh của bạn, Kutools cho Excel is một công cụ hoàn hảo trong Excel để giúp bạn. Với tích hợp của nó Thay thế 0 hoặc # N / A bằng tính năng Trống hoặc Giá trị cụ thể, bạn có thể chỉ định thông báo không tìm thấy mà không cần công thức phức tạp hoặc quyền truy cập vào XLOOKUP.

với chúng tôi Bổ trợ Excel đã cài đặt, vui lòng làm như sau:

1. Đi đến Kutools tab trong Excel, tìm SIÊU NHÌN, Và nhấp vào Thay thế 0 hoặc # N / A bằng Giá trị trống hoặc Giá trị cụ thể trong danh sách thả xuống.

hàm xlookup 30

2. bên trong Thay thế 0 hoặc # N / A bằng hộp thoại Trống hoặc Giá trị cụ thể, bạn cần cấu hình như sau:

  • Trong tạp chí Tra cứu giá trị và phần phạm vi đầu ra, lựa chọn phạm vi tra cứuPhạm vi đầu ra;
  • Sau đó chọn tùy chọn Thay thế 0 hoặc # N / A bằng một Giá trị cụ thể, nhập văn bản bạn thích;
  • Trong tạp chí Phần phạm vi dữ liệu, chọn nút phạm vi dữ liệu, sau đó chỉ định cột chínhcột trả về.

hàm xlookup 31

3. Nhấp vào OK để nhận kết quả. Thông báo tùy chỉnh sẽ được hiển thị khi không tìm thấy kết quả phù hợp.

hàm xlookup 32

Nhấp để tải xuống Kutools cho Excel để dùng thử miễn phí 30 ngày.


Ví dụ 8: Nhiều giá trị

Trả về nhiều giá trị với XLOOKUP

Một lợi thế của XLOOKUP là khả năng trả về nhiều giá trị đồng thời cho cùng một trận đấu. Nhập một công thức để nhận kết quả đầu tiên, sau đó nhập các giá trị trả về khác tràn tự động vào các ô trống lân cận.

Trong ví dụ dưới đây, bạn muốn nhận tất cả thông tin về thẻ sinh viên “FG9940005”. Bí quyết là cung cấp một dải ô dưới dạng dải_trái trong công thức thay vì một cột hoặc một hàng. Trong trường hợp này, phạm vi mảng trả về là B2: D9, bao gồm ba cột.

công thức dưới đây trong ô trống G2, nhấn Đi vào chìa khóa để nhận được tất cả các kết quả.

=XLOOKUP(F2,A2:A9,B2:D9)

hàm xlookup 33

Tất cả các ô kết quả hiển thị cùng một công thức. Bạn có thể chỉnh sửa hoặc sửa đổi công thức trong ô đầu tiên, Nhưng trong các ô khác, công thức không thể chỉnh sửa được. Bạn có thể thấy thanh công thức là xám xịt, nghĩa là không thể thực hiện thay đổi đối với nó.

hàm xlookup 34

Nói chung, hàm nhiều giá trị của XLOOKUP là một cải tiến hữu ích so với hàm VLOOKUP. Bạn không cần phải chỉ định từng số cột riêng biệt cho từng công thức. Thích!

Ví dụ 9. Nhiều tiêu chí

Thực hiện tra cứu nhiều tiêu chí bằng XLOOKUP

Một tính năng mới tuyệt vời của XLOOKUP là khả năng tra cứu với nhiều tiêu chí. Bí quyết là nối giá trị tra cứu và mảng tra cứu với "&" nhà điều hành riêng biệt trong công thức. Hãy minh họa qua ví dụ dưới đây.

Chúng ta cần biết giá của chiếc bình lam vừa. Trong trường hợp này, cần có ba giá trị tra cứu (tiêu chí) để tìm kiếm kết quả phù hợp. Gõ công thức bên dưới trong ô trống I2, sau đó nhấn Đi vào phím để nhận kết quả.

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

hàm xlookup 35

√ Lưu ý: XLOOKUP có thể xử lý trực tiếp các mảng. Không cần xác nhận công thức bằng Control + Shift + Enter.

Tra cứu nhiều điều kiện với một phương pháp nhanh chóng

Có cái nào không nhanh hơn và dễ dàng hơn cách thực hiện tra cứu đa tiêu chí so với XLOOKUP trong excel? Kutools cho Excel cung cấp một tính năng tuyệt vời - Tra cứu đa điều kiện. Với tính năng này, bạn có thể chạy tra cứu nhiều tiêu chí chỉ với vài cú nhấp chuột!

với chúng tôi Bổ trợ Excel đã cài đặt, vui lòng làm như sau:

1. Đi đến Kutools tab trong Excel, tìm SIÊU NHÌN, Và nhấp vào Tra cứu đa điều kiện trong danh sách thả xuống.

hàm xlookup 36

2. bên trong Hộp thoại Tra cứu đa điều kiện, vui lòng làm như sau:

  • Trong tạp chí Phần giá trị tra cứu và phạm vi đầu ra, chọn nút phạm vi giá trị tra cứu Phạm vi đầu ra;
  • Trong tạp chí Phần Dải dữ liệu, vui lòng thực hiện các thao tác sau:
    • Chọn hình ba gạch các cột chính tương ứng chứa lần lượt các giá trị tra cứu bằng cách giữ Ctrl chìa khóa trong Hộp cột chính;
    • Xác định cột chứa các giá trị trả về trong Hộp cột trả lại.

hàm xlookup 37

3. Nhấp vào OK để nhận kết quả.

hàm xlookup 38

√ Lưu ý:

  • Giá trị lỗi Replace # N / A bằng phần giá trị được chỉ định là tùy chọn trong hộp thoại, bạn có thể chỉ định hoặc không.
  • Số cột được nhập trong hộp Cột khóa phải bằng số cột được nhập trong hộp Giá trị tra cứu và thứ tự của tiêu chí trong cả hai hộp phải tương ứng với nhau.

Nhấp để tải xuống Kutools cho Excel để dùng thử miễn phí 30 ngày.


Ví dụ 10. Tìm giá trị với kết quả phù hợp cuối cùng

Nhận kết quả phù hợp cuối cùng với XLOOKUP

Để tìm giá trị phù hợp cuối cùng trong Excel, hãy đặt đối số thứ sáu trong hàm XLOOKUP để tìm kiếm theo thứ tự ngược lại.

Theo mặc định, chế độ tìm kiếm trong XLOOKUP được đặt thành 1, Đó là tìm kiếm từ đầu tiên đến cuối cùng. Nhưng điều tốt về XLOOKUPhướng tra cứu có thể được thay đổi. XLOOKUP cung cấp đối số [chế độ tìm kiếm] tùy chọn để kiểm soát thứ tự tìm kiếm. Chỉ cần đặt chế độ tìm kiếm trong đối số thứ sáu thành -1, hướng tra cứu được thay đổi để tìm kiếm từ cuối cùng đến đầu tiên.

Vui lòng xem ví dụ bên dưới. Chúng tôi muốn biết lần bán Emma cuối cùng trong cơ sở dữ liệu.

công thức bên dưới trong ô trống G2, sau đó nhấn Đi vào phím để nhận kết quả.

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

hàm xlookup 39

√ Lưu ý: Đối số thứ tư và thứ năm là tùy chọn và bị bỏ qua trong trường hợp này. Chúng tôi chỉ đặt đối số thứ sáu tùy chọn thành -1.

Dễ dàng tra cứu giá trị khớp cuối cùng với một công cụ tuyệt vời

Trong trường hợp bạn không có quyền truy cập vào XLOOKUP và cũng không muốn nhớ các công thức phức tạp, bạn có thể áp dụng Tra cứu tính năng từ dưới lên trên of Kutools cho Excel để hoàn thành nó dễ dàng.

với chúng tôi Bổ trợ Excel đã cài đặt, vui lòng làm như sau:

1. Đi đến Kutools tab trong Excel, tìm SIÊU NHÌN, Và nhấp vào Tra cứu từ dưới lên trên trong danh sách thả xuống.

hàm xlookup 40

2. bên trong LOOKUP từ dưới lên trên hộp thoại, bạn cần cấu hình như sau:

  • Trong tạp chí Tra cứu giá trị và phần phạm vi đầu ra, chọn nút phạm vi tra cứuPhạm vi đầu ra;
  • Trong tạp chí Phần phạm vi dữ liệu, chọn nút phạm vi dữ liệu, sau đó chỉ định cột chínhcột trả lại.

hàm xlookup 41

3. Nhấp vào OK để nhận kết quả.

hàm xlookup 42

√ Lưu ý: Giá trị lỗi Replace # N / A với phần giá trị được chỉ định là tùy chọn trong hộp thoại, bạn có thể chỉ định hoặc không.

Nhấp để tải xuống Kutools cho Excel để dùng thử miễn phí 30 ngày.


Tải xuống tệp mẫu XLOOKUP

XLOOKUP Examples.xlsx

Các bài liên quan:



  • Super Formula Bar (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột và Lưu giữ dữ liệu; Nội dung phân chia ô; Kết hợp các hàng trùng lặp và Tổng / Trung bình... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Yêu thích và Chèn công thức nhanh chóng, Dãy, Biểu đồ và Hình ảnh; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Nhóm bảng tổng hợp theo số tuần, ngày trong tuần và hơn thế nữa ... Hiển thị các ô đã mở khóa, đã khóa bởi các màu sắc khác nhau; Đánh dấu các ô có công thức / tênhữu ích. Cảm ơn !
tab kte 201905
  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Nhận xét (0)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Không có bình luận nào được đăng ở đây
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0  Nhân vật
Các vị trí được đề xuất