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 phạm vi 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.
Để trả về kết quả từ một dãy, hãy sử dụng INDEX với XLOOKUP.
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:  Trong công thức này, các đối số sau đây được sử dụng:
|
Đặ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":  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:
|
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:  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:
|
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:  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:
|
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:  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":  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". =INDEX(XLOOKUP(A2;A2:A11;B2:C11);2) trả về 71000, giá trị thứ hai trong dãy được trả về bởi XLOOKUP. |