31.8.19
Trong ví dụ này, chúng tôi đang tìm kiếm thông tin nhân viên dựa trên số ID nhân viên. Không giống như VLOOKUP, XLOOKUP có thể trả về một mảng với nhiều mục, cho phép một công thức duy nhất để trả về cả tên nhân viên và bộ phận.
Cú pháp
Hàm XLOOKUP tìm kiếm một phạm vi hoặc một mảng và trả về một mục tương ứng với khớp đầu tiên nó tìm thấy. Nếu khớp không tồn tại, thì XLOOKUP có thể trả về kết quả khớp gần nhất (xấp xỉ).
XLOOKUP (lookup_value, mảng tìm kiếm, return_array, [match_mode], [search_mode])
Đối số
|
Mô tả
|
|---|---|
lookup_value
|
Giá trị tra cứu
|
mảng tìm kiếm
|
Mảng hoặc dải ô cần tìm kiếm
|
return_array
|
Mảng hoặc dải ô cần trả về
|
match_mode
Tùy chọn
|
Xác định kiểu khớp:
0-khớp chính xác. Nếu không tìm thấy gì, hãy trả về #N/A. Đây là tùy chọn mặc định.
-1-khớp chính xác. Nếu không tìm thấy gì, hãy trả về mục nhỏ hơn tiếp theo.
1-khớp chính xác. Nếu không tìm thấy gì, hãy trả về mục lớn hơn tiếp theo.
2-một ký tự đại diện trong đó *,?, và ~ có ý nghĩa đặc biệt.
|
search_mode
Tùy chọn
|
Xác định chế độ tìm kiếm để sử dụng:
1-thực hiện tìm kiếm bắt đầu từ mục đầu tiên. Đây là tùy chọn mặc định.
-1-thực hiện tìm kiếm đảo ngược bắt đầu ở mục cuối cùng.
2-thực hiện tìm kiếm nhị phân dựa trên mảng tìm kiếm đang được sắp xếp theo thứ tự tăng dần . Nếu không được sắp xếp, kết quả không hợp lệ sẽ được trả về.
-2-thực hiện tìm kiếm nhị phân dựa trên mảng tìm kiếm đang được sắp xếp theo thứ tự giảm dần . Nếu không được sắp xếp, kết quả không hợp lệ sẽ được trả về.
|
Ví dụ
Ví dụ 1
Ví dụ sau đây sử dụng XLOOKUP đơn giản để tìm kiếm một tên quốc gia và trả về mã quốc gia của điện thoại. Nó chỉ bao gồm lookup_value (ô F2), mảng tìm kiếm (phạm vi B2: B11), và return_array (phạm vi D2: D11) đối số. Nó không bao gồm đối số match_mode, như nó mặc định là kết quả khớp chính xác.
Lưu ý: XLOOKUP khác với hàm VLOOKUP trong đó nó dùng các mảng tra cứu và trả về riêng biệt, trong đó VLOOKUP sử dụng một mảng bảng đơn, theo sau là một số chỉ số cột. Công thức VLOOKUP tương đương trong trường hợp này sẽ là: = VLOOKUP (F2, B2: D11, 3, FALSE)
Ví dụ 2
Ví dụ sau đây sẽ hiển thị trong cột C cho thu nhập cá nhân được nhập vào ô E2 và tìm thấy tỷ suất thuế kết hợp trong cột B. Nó dùng đối số match_mode đặt thành 1, có nghĩa là hàm sẽ tìm kiếm kết quả phù hợp chính xác và nếu nó không thể tìm thấy một, nó sẽ trả về mục lớn hơn tiếp theo.
Lưu ý: Không giống như VLOOKUP, cột mảng tìm kiếm nằm bên phải của cột return_array, trong đó VLOOKUP chỉ có thể nhìn từ trái sang phải.
Ví dụ 3
Tiếp theo, chúng tôi sẽ sử dụng hàm XLOOKUP lồng nhau để thực hiện cả khớp dọc và ngang. Trong trường hợp này, trước tiên, nó sẽ tìm kiếm lợi nhuận gộp trong cột B, sau đó tìm Qtr1 ở hàng trên cùng của bảng (phạm vi C5: F5) và trả về giá trị tại giao điểm của hai. Điều này tương tự như việc sử dụng các hàm index và match kết hợp .
Công thức trong các ô D3: F3 là: = XLOOKUP (D2, $B 6: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17)).
Ví dụ 4
Ví dụ này sử dụng hàm Sumvà hai hàm xlookup được lồng với nhau để tính tổng tất cả các giá trị giữa hai phạm vi. Trong trường hợp này, chúng tôi muốn tính tổng các giá trị cho nho, chuối và lê, nằm giữa hai.
Công thức trong ô E3 là: = SUM (XLOOKUP (C3, C6: C10, F6: F10): XLOOKUP (D3, C6: C10, F6: F10))
Tính năng này hoạt động như thế nào? XLOOKUP trả về một phạm vi, vì vậy khi tính năng này tính toán, công thức sẽ kết thúc việc trông như thế này: = SUM ($F $7: $F $9). Bạn có thể thấy cách làm việc này trên riêng của bạn bằng cách chọn một ô có công thức XLOOKUP tương tự như phần này, rồi đi đến công thức > kiểm nghiệm công thức > đánh giá công thứcvà nhấn nút đánh giá để bước qua tính toán.
Lưu ý: Nhờ Microsoft Excel MVP, Bill Jelen, cho thấy ví dụ này.
Không có nhận xét nào
Đăng nhận xét