Formulas and Functions
Sửa đổi điều khiển này sẽ tải lại trang này

XLOOKUP

Hàm XLOOKUP tìm kiếm một vùng giá trị được chỉ định và trả về giá trị từ cùng một hàng trong một cột khác.

XLOOKUP(giá trị tìm kiếm; vùng tìm kiếm; vùng trả về; nếu không tìm thấy; loại kết quả phù hợp; loại tìm kiếm)

giá trị tìm kiếm: Giá trị đang được tìm kiếm trong dãy tìm kiếm. giá trị tìm kiếm có thể chứa bất kỳ giá trị nào hoặc chuỗi REGEX.

vùng tìm kiếm: Các ô sẽ tìm kiếm.

vùng trả về: Các ô sẽ trả về.

nếu không tìm thấy: Một đối số tùy chọn để chỉ định thông báo hiển thị nếu không tìm thấy kết quả phù hợp.

loại kết quả phù hợp: Một đối số tùy chọn chỉ định loại kết quả phù hợp sẽ tìm kiếm.

chính xác hoặc nhỏ nhất tiếp theo (-1): Nếu không có kết quả, trả lại lỗi.

kết quả chính xác (0 hoặc bỏ qua): Nếu không có kết quả khớp hoàn toàn, trả lại lỗi.

chính xác hoặc lớn nhất tiếp theo (1): Nếu không có kết quả, trả lại lỗi.

ký tự đại diện (2): *, ? và ~ có ý nghĩa riêng. Chỉ có thể sử dụng REGEX trong XLOOKUP nếu bạn sử dụng ký tự đại diện.

loại tìm kiếm: Một đối số tùy chọn chỉ định thứ tự tìm kiếm trong vùng.

Nhị phân giảm dần (-2): Tìm kiếm nhị phân yêu cầu sắp xếp vùng theo thứ tự giảm dần, ngược lại sẽ trả về lỗi.

Cuối đến đầu (-1): Tìm kiếm trong vùng từ cuối cùng đến đầu tiên.

Đầu đến cuối (1 hoặc bỏ qua): Tìm kiếm trong vùng từ đầu tiên đến cuối cùng.

Nhị phân tăng dần (2): Tìm kiếm nhị phân yêu cầu sắp xếp vùng theo thứ tự tăng dần, ngược lại sẽ trả về lỗi.

Ghi chú

  • Nếu vùng tìm kiếm hoặc vùng trả về là một tham chiếu mở rộng (ví dụ như "B"), phần đầu và phần chân được bỏ qua tự động.

Ví dụ

Bảng bên dưới, có tiêu đề Các sản phẩm, liệt kê các sản phẩm và các thuộc tính của chúng, chẳng hạn như kích cỡ và giá:

A

B

C

D

E

1

Sản phẩm

Chiều dài (cm)

Chiều rộng (cm)

Trọng lượng (kg)

Giá

2

Sản phẩm 1

16

17

10

$82,00

3

Sản phẩm 2

16

20

18

$77,00

4

Sản phẩm 3

11

11

15

$88,00

5

Sản phẩm 4

15

16

20

$63,00

Tìm kiếm bằng XLOOKUP

Với XLOOKUP, bạn có thể chèn công thức vào bảng tính trả về giá trị được kết hợp bất kỳ bằng cách cung cấp tên sản phẩm trước tiên, sau đó cột có giá trị bạn muốn trả về. Ví dụ: nếu bạn đã muốn trả về chiều rộng của Sản phẩm 1 trong bảng phía trên, bạn có thể sử dụng công thức sau đây, sẽ trả về 17 cm:

Trình sửa công thức đang hiển thị công thức =XLOOKUP(Các sản phẩm::$A2;Các sản phẩm::A;Chiều rộng).

Trong công thức này, các đối số sau đây được sử dụng:

  • giá trị tìm kiếm: Các sản phẩm::$A2, tham chiếu tuyệt đối đối với ô trong bảng Các sản phẩm chứa Sản phẩm 1.

  • vùng tìm kiếm: Các sản phẩm::A, cột để tìm kiếm Sản phẩm 1.

  • vùng trả về: Chiều rộng, cột chứa giá trị trả về được kết hợp với Sản phẩm 1.

  • loại kết quả phù hợp: Được bỏ qua. Nếu loại kết quả phù hợp được bỏ qua, XLOOKUP sẽ tìm kiếm phù hợp hoàn toàn theo mặc định.

Đặt chuỗi nếu không tìm thấy

Nếu bạn đã muốn tìm kiếm chiều dài sản phẩm cụ thể và trả về chiều rộng phù hợp của sản phẩm đó cũng như chuỗi trả về nếu không tìm thấy kết quả phù hợp thì bạn có thể sử dụng công thức sau đây, sẽ trả về "Không có kết quả phù hợp":

Trình sửa công thức đang hiển thị công thức =XLOOKUP(13;Chiều dài;Chiều rộng;"Không có kết quả phù hợp";0).

Trong công thức này, đối số nếu không tìm thấy được sử dụng để thực hiện tìm kiếm cụ thể hơn:

  • giá trị tìm kiếm: 13, giá trị tìm kiếm trong vùng được chỉ định trong vùng tìm kiếm.

  • vùng tìm kiếm: Chiều dài, cột để tìm kiếm trong đó.

  • vùng trả về: Chiều rộng, cột chứa giá trị trả về nếu tìm thấy kết quả phù hợp cho giá trị tìm kiếm.

  • nếu không tìm thấy: "Không có kết quả phù hợp", chuỗi hiển thị nếu không tìm thấy sản phẩm nào có chiều dài là 13 cm.

  • loại kết quả phù hợp: phù hợp hoàn toàn (0). Tìm kiếm này chỉ dành cho chiều dài là 13 cm.

Tìm giá trị gần nhất tiếp theo

XLOOKUP cũng có thể cung cấp tìm kiếm rộng lớn dựa vào giá trị cụ thể và các giá trị gần với giá trị đó. Nếu bạn thay đổi loại kết quả phù hợp từ công thức phía trên, bạn có thể trả về chiều rộng khớp với chiều dài là 13 cm hoặc giá trị nhỏ nhất tiếp theo. Công thức bên dưới trả về chiều rộng là 11 cm:

Trình sửa công thức đang hiển thị công thức =XLOOKUP(13;Chiều dài;Chiều rộng;"Không có kết quả phù hợp";1;-1).

Trong công thức này, các đối số giống phía trên, ngoại trừ giá trị khác nhau được sử dụng cho loại kết quả phù hợp để thay đổi cách tìm kiếm bảng:

  • loại kết quả phù hợp: chính xác hoặc nhỏ nhất tiếp theo (-1). Tìm kiếm này dành cho chiều dài là 13 cm và nếu không tìm thấy giá trị đó thì hãy tìm giá trị nhỏ nhất tiếp theo trong cột Chiều dài.

Thay đổi thứ tự tìm kiếm

Trong một số trường hợp, có thể hữu ích khi thay đổi thứ tự trong đó bảng được tìm kiếm bằng XLOOKUP. Ví dụ: trong bảng phía trên, có hai sản phẩm có chiều dài là 16 cm, do đó có hai kết quả phù hợp tiềm tàng nếu bạn tìm kiếm 16 cm trong cột Chiều dài bằng giá trị tìm kiếm và vùng tìm kiếm. Bạn có thể đặt thứ tự tìm kiếm bằng công thức như sau, sẽ trả về 20 cm:

Trình sửa công thức đang hiển thị công thức =XLOOKUP(16;Chiều dài;Chiều rộng;"Không có kết quả phù hợp";1;-1).

Trong công thức này, đối số loại tìm kiếm được sử dụng để đặt thứ tự trong đó XLOOKUP tìm kiếm bảng để có kết quả phù hợp:

  • giá trị tìm kiếm: 16, giá trị tìm kiếm trong vùng được chỉ định trong vùng tìm kiếm.

  • vùng tìm kiếm: Chiều dài, cột để tìm kiếm trong đó.

  • vùng trả về: Chiều rộng, cột chứa giá trị trả về nếu tìm thấy kết quả phù hợp cho giá trị tìm kiếm.

  • nếu không tìm thấy: "Không có kết quả phù hợp", chuỗi hiển thị nếu không tìm thấy sản phẩm nào có chiều dài là 16 cm.

  • loại kết quả phù hợp: chính xác hoặc lớn nhất tiếp theo (1). Tìm kiếm này dành cho chiều dài là 16 cm và nếu không tìm thấy giá trị đó thì hãy tìm giá trị lớn nhất tiếp theo trong cột Chiều dài.

  • loại tìm kiếm: Cuối đến đầu (-1). Việc này sẽ tìm kiếm cột từ giá trị cuối cùng đến giá trị đầu tiên.

Sử dụng XLOOKUP với các hàm khác

XLOOKUP có thể được sử dụng với các hàm khác, chẳng hạn như SUM. Ví dụ: bạn có thể sử dụng công thức như công thức bên dưới để trả về $247, SUM của giá Các sản phẩm 1, 2 và 3:

Trình sửa công thức đang hiển thị công thức =SUM(XLOOKUP(Các sản phẩm::$A2;Các sản phẩm::A;Giá):XLOOKUP(Các sản phẩm::$A4;Các sản phẩm::A;Giá)).

Trong ví dụ này, XLOOKUP đầu tiên tìm kiếm giá của Sản phẩm 1 và XLOOKUP thứ hai tìm kiếm giá của Sản phẩm 3. Dấu hai chấm (:) giữa các hàm XLOOKUP cho biết SUM sẽ trả về không chỉ tổng giá của Sản phẩm 1 và Sản phẩm 3 mà còn trả về bất kỳ giá trị nào nằm giữa.

Trong công thức bên dưới, XLOOKUP được sử dụng với REGEX để trả về Sản phẩm 2, sản phẩm đầu tiên có chiều rộng bắt đầu với "2":

Trình sửa công thức đang hiển thị công thức =XLOOKUP(REGEX("^2.*"); Các sản phẩm::C2:C5; Các sản phẩm::A2:A5; FALSE;2).

Trong ví dụ này, "ký tự đại diện (2)" được sử dụng cho loại kết quả phù hợp để sử dụng ký tự đại diện trong hàm REGEX.

Các ví dụ khác

Cho bảng sau:

A

B

C

1

Tên

Tuổi

Lương

2

Amy

35

71000

3

Matthew

27

81000

4

Chloe

42

86000

5

Sophia

51

66000

6

Kenneth

28

52000

7

Tom

49

62000

8

Aaron

63

89000

9

Mary

22

34000

10

Alice

29

52000

11

Brian

35

52500

=XLOOKUP(49;B2:B11;C2:C11) trả về "62000" là lương của nhân viên đầu tiên có tuổi bằng 49.

=XLOOKUP(60000;C2:C11;B2:B11;"Không có kết quả phù hợp") trả về "Không có kết quả phù hợp" vì không có nhân viên nào có mức lương bằng $60.000.

=XLOOKUP(REGEX("^C.*"); A2:A11; B2:B11; FALSE; 2) trả về "42" là tuổi của "Chloe" nhân viên đầu tiên trong vùng có tên bắt đầu bằng chữ "C".

Xem thêmXMATCH
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
Có ích?
Giới hạn ký tự: 250
Giới hạn tối đa là 250 ký tự.
Cảm ơn phản hồi của bạn.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.