Sử dụng hàm XLOOKUP để tìm văn bản trong một bảng hoặc dải ô theo hàng. Ví dụ: tra cứu giúp giá của linh kiện ô đánh theo số linh kiện hoặc tìm kiếm tên nhân viên cấp dưới dựa bên trên ID nhân viên của họ. Với hàm XLOOKUP, chúng ta cũng có thể tìm từ search kiếm trong một cột và trả về tác dụng từ cùng một hàng vào cột khác, bất cứ cột trả về đang ở mặt nào.
Bạn đang xem: Hàm tìm kiếm trả về nhiều giá trị
Lưu ý: XLOOKUP không sẵn dùng trong Excel năm 2016 và Excel 2019, mặc dù nhiên, chúng ta cũng có thể gặp phải tình huống sử dụng sổ làm việc trong Excel năm nhâm thìn hoặc Excel 2019 cùng với hàm XLOOKUP trong số ấy được tạo bởi tín đồ khác bằng cách dùng phiên bản Excel bắt đầu hơn.
Cú pháp
Hàm XLOOKUP kiếm tìm kiếm một dải ô hoặc một mảng, rồi trả về mục tương ứng với quý giá khớp đầu tiên tìm được. Nếu như không tồn tại hiệu quả khớp làm sao thì hàm XLOOKUP có thể trả về hiệu quả khớp gần nhất (gần đúng).
=XLOOKUP(lookup_value, lookup_array, return_array,
| lookup_value Yêu cầu* | Giá trị bắt buộc tìm kiếm*Nếu quăng quật qua, hàm XLOOKUP trả về các ô trống tra cứu thấy trong lookup_array. |
| mảng tìm kiếm Bắt buộc | Mảng hoặc dải ô nên tìm kiếm |
| return_array Bắt buộc | Mảng hoặc phạm vi phải trả về |
| Tôi ko if_not_found tôi. Tùy chọn | Trong trường hợp không kiếm thấy hiệu quả khớp thích hợp lệ, trả về văn phiên bản Nếu không kiếm thấy hiệu quả khớp hợp lệ với |
| Tôi không match_mode tôi. Tùy chọn | Xác định mẫu mã khớp: 0 - Trận đấu thiết yếu xác. Nếu không tìm thấy tin tức nào, #N/A. Đây là tùy chọn mặc định. -1 - Trận đấu bao gồm xác. Nếu không tìm kiếm thấy, hãy trả về mục bé dại tiếp theo. 1 - Trận đấu chính xác. Nếu không tìm kiếm thấy, hãy trả về mục lớn hơn tiếp theo. 2 - Một phối hợp ký tự thay mặt trong đó *, ?, và ~ có ý nghĩa đặc biệt. |
| Tôi ko search_mode tôi. Tùy chọn | Chỉ định chế độ tìm kiếm phải sử dụng: 1 - thực hiện tìm kiếm ban đầu từ mục đầu tiên. Đây là tùy lựa chọn mặc định. -1 - thực hiện tìm kiếm đảo ngược bắt đầu từ mục cuối cùng. 2 - thực hiện tìm tìm nhị phân dựa vào lookup_array bố trí theo thiết bị tự tăng mạnh . Nếu như không được chuẩn bị xếp, tác dụng không hợp lệ sẽ được trả về. -2 - thực hiện một tìm kiếm kiếm nhị phân dựa vào lookup_array được thu xếp theo trang bị tự sút dần . Còn nếu như không được chuẩn bị xếp, kết quả không đúng theo lệ sẽ tiến hành trả về. |
Ví dụ
Ví dụ 1 dùng XLOOKUP để tra cứu giúp tên quốc gia trong một dải ô, rồi trả về mã tổ quốc điện thoại của nước nhà đó. Nó bao gồm các lookup_value (ô F2), lookup_array (phạm vi B2:B11) và những đối return_array (phạm vi D2:D11). Hàm này không bao hàm tham đối match_mode do XLOOKUP cho kết quả khớp chính xác theo mang định.
Lưu ý: XLOOKUP dùng mảng tra cứu cùng mảng trả về, trong khi VLOOKUP sử dụng một mảng bảng tốt nhất theo sau là số chỉ mục cột. Công thức VLOOKUP tương tự trong trường hợp này vẫn là: =VLOOKUP(F2,B2:D11,3,FALSE)
———————————————————————————
Ví dụ 2 tra cứu tin tức nhân viên dựa trên số ID nhân viên. Không y hệt như hàm VLOOKUP, XLOOKUP rất có thể trả về mảng có rất nhiều mục, vị vậy một cách làm duy nhất rất có thể trả về cả tên nhân viên cấp dưới và cơ sở từ các ô C5:D14.
———————————————————————————
Ví dụ 3 thêm 1 if_not_found đối số vào lấy một ví dụ trước đó.
———————————————————————————
Ví dụ 4 vào cột C mang đến thu nhập cá nhân được nhập vào ô E2 cùng tìm thuế suất khớp ngơi nghỉ cột B. Nó đặt tham đối if_not_found trả về 0 (không) nếu không kiếm thấy gì. Tham match_mode của hàm được để thành 1, tức là hàm sẽ tìm kiếm kết quả khớp đúng đắn và nếu không tìm thấy công dụng khớp, nó trả về mục to hơn tiếp theo. Cuối cùng, tham search_mode được để thành 1, có nghĩa là hàm đang tìm kiếm từ mục thứ nhất đến mục cuối cùng.
Lưu ý: Cột lookup_array của XARRAY nằm ở bên bắt buộc cột return_array, trong khi VLOOKUP chỉ rất có thể nhìn từ trái thanh lịch phải.
———————————————————————————
Ví dụ 5 dùng hàm XLOOKUP được lồng để tiến hành khớp dọc với ngang. Trước tiên, nó search Kiếm lợi nhuận Gộp vào cột B, sau đó tìm Q1 ở sản phẩm trên cùng của bảng (phạm vi C5:F5) và sau cuối trả về giá trị tại giao điểm của nhì bảng. Điều này tương tự như sử dụng phối hợp hàm INDEX cùng MATCH .
———————————————————————————
Ví dụ 6 sử dụng hàm SUM và hai hàm XLOOKUP lồng nhau để tính tổng toàn bộ các cực hiếm giữa hai dải ô. Trong trường vừa lòng này, chúng ta muốn tính tổng các giá trị mang lại nho, chuối và bao hàm lê, nằm trong lòng hai giá trị này.
Công thức vào ô E3 là: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Cách thức hoạt động? Hàm XLOOKUP trả về một phạm vi, bởi vì vậy khi tính toán, phương pháp sẽ trông hệt như thế này: =SUM($E$7:$E$9). Bạn cũng có thể tự mình xem phương pháp hoạt động bằng cách chọn một ô bao gồm công thức XLOOKUP tương tự như như cách làm này, rồi lựa chọn Công thức > Kiểm định Công thức >Đánh giá bán Công thức, rồi lựa chọn Đánh giá chỉ để thực hiện từng cách tính toán.
———————————————————————————
Bạn yêu cầu thêm trợ giúp?
Bạn luôn hoàn toàn có thể hỏi một chuyên viên trong cộng đồng Kỹ thuật Excel hoặc dìm sự cung cấp trong
Cộng đồng trả lời.
Làm nuốm nào để trả về những hàng từ một giá trị? Điều này là rất có thể và rất giản đơn dàng, nhưng không phải với hàm VLOOKUP.
VLOOKUP KHÔNG PHẢI LÀ HÀM ĐỂ SỬ DỤNG
Hàm VLOOKUP chưa lúc nào được xây dựng để trả về nhiều hàng, và thậm chí là cả hàm INDEX. Mặc dù các hàm này được sử dụng rộng thoải mái trong bảng tính, mà lại chỉ rất có thể trả về một hiệu quả duy nhất.
Cách duy nhất để trả về các dòng xuất phát từ một giá trị là sử dụng hàm FILTER.
HÀM FILTER
Hàm FILTER chỉ khả dụng mang đến Microsoft 365 hoặc Excel Online. FILTER là một phần của những hàm cách xử lý về mảng và đấy là những hàm new được reviews vào năm 2019, có thể chấp nhận được bạn trả về một công dụng trong các ô chứ không những một.
VÍ DỤ VỚI TRACKING NUMBER
Dưới đây, họ sẽ gồm tracking time cho những gói khác biệt (tracking number). Tracking number là chìa khóa. Một tracking number hoàn toàn có thể được tạo nên thành từ một trong những gói.
Chúng ta mong tìm tất cả các tài liệu xem thêm về thành phầm để tạo thành một lô hàng.
Bước 1: chọn cột nhằm trả về
Chúng ta sẽ thực hiện hàm FILTER nhằm chỉ trả về cột tham chiếu sản phẩm, vì vậy đối số đầu tiên của hàm là cột B.
| =FILTER(B2:B48 |
Bước 2: Viết điều kiện của Filter
Bây giờ, họ chỉ ra các tiêu chí để áp dụng. Điều này rất đối chọi giản.
Chọn cột chứa những giá trị để lọcCho biết giá bán trị của bộ lọc
| =FILTER(B2:B48;$A$2:$A$48=G2) |
Bước 3: Xóa các giá trị trùng lặp
Như bạn có thể thấy, hàm trả về các bản sao. Để kị điều này, chúng ta có thể nhúng cách làm trước kia vào hàm UNIQUE.
Sự kết hợp của hàm FILTER và chất lượng trả về đúng đắn số lượng cực hiếm chỉ vào 3 bước.
Nếu đổi khác tracking number, ngay lập tức, hàm trả về một kết quả khác (ở đây là 4 hàng).
Xem thêm: Truyện Tranh Hắc Quản Gia Chap 1 Next Chap 2, Truyện Tranh Hắc Quản Gia Chap 1
Một ví dụ như khác với tương đối nhiều hàng
Bây giờ, chúng ta muốn trả lại 2 thông tin:
Poste ID (cột C)Tracking date và tracking time (cột D)Cái tốt của hàm FILTER là bạn cũng có thể sử dụng nhiều hơn nữa một cột nhằm trả về.