Cách sử dụng hàm XLOOKUP trong Excel 2016

Người đăng ký Excel 2016 dành cho Office 365 trên Windows và Mac hiện hỗ trợ một hàm XLOOKUP mới, được quảng cáo là một sự thay thế linh hoạt và đơn giản hơn đáng kể cho hàm tra cứu theo chiều dọc rất phổ biến (nhưng không đúng), VLOOKUP (không biết chữ X trong XLOOKUP là gì là viết tắt của; eXtensive, có lẽ?).

Đối với những người bạn chưa quen với hàm VLOOKUP (được coi là hàm được sử dụng nhiều thứ ba ngay sau hàm SUM và AVERAGE), hàm này tìm kiếm theo chiều dọc theo hàng trong cột ngoài cùng bên trái của bảng tra cứu được chỉ định từ trên xuống dưới cho đến khi tìm thấy giá trị trong cột tra cứu được chỉ định bởi một số bù trùng hoặc vượt quá cột tra cứu của bạn. Mặc dù rất hữu ích để định vị các mục cụ thể trong một danh sách dài hoặc cột của bảng dữ liệu trong trang tính của bạn, nhưng hàm VLOOKUP có một số hạn chế không được hàm tra cứu mới này chia sẻ, như XLOOKUP:

• Mặc định tìm các kết quả phù hợp chính xác cho giá trị tra cứu của bạn trong phạm vi tra cứu
• Có thể tìm kiếm theo cả chiều dọc (theo hàng) và chiều ngang (theo cột) trong bảng, do đó thay thế nhu cầu sử dụng hàm HLOOKUP khi tìm kiếm theo chiều ngang theo cột
• Có thể tìm kiếm bên trái hoặc bên phải để phạm vi tra cứu trong bảng tra cứu của bạn không phải nằm trong cột bên trái của cột được chỉ định là phạm vi trả về để hàm hoạt động
• Khi sử dụng mặc định đối sánh chính xác, sẽ hoạt động ngay cả khi các giá trị trong phạm vi tra cứu không được sắp xếp theo thứ tự cụ thể
• Có thể tìm kiếm từ hàng dưới cùng lên trên cùng trong phạm vi mảng tra cứu, sử dụng đối số chế độ tìm kiếm tùy chọn

Hàm XLOOKUP có năm đối số có thể có, ba đối số đầu tiên là bắt buộc và hai đối số cuối cùng là tùy chọn, sử dụng cú pháp sau:

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

Đối số lookup_value bắt buộc chỉ định giá trị hoặc mục mà bạn đang tìm kiếm. Đối số mảng look_up bắt buộc chỉ định phạm vi ô được tìm kiếm cho giá trị tra cứu này và đối số return_array chỉ định phạm vi ô chứa giá trị bạn muốn trả về khi Excel tìm thấy một kết quả khớp chính xác.

* Hãy nhớ khi chỉ định đối số lookup_array và return_array trong hàm XLOOKUP của bạn, cả hai phạm vi phải có độ dài bằng nhau, nếu không Excel sẽ trả về giá trị lỗi #VALUE! lỗi đối với công thức của bạn. Đây hoàn toàn là lý do để bạn sử dụng tên dải ô hoặc tên cột của bảng dữ liệu được chỉ định khi xác định các đối số này thay vì trỏ chúng ra ngoài hoặc nhập vào tham chiếu ô của chúng .

Đối số match_mode tùy chọn có thể chứa bất kỳ giá trị nào trong số bốn giá trị sau:

• 0 cho một đối sánh chính xác (mặc định, giống như khi không có đối số match_mode nào được chỉ định)
• -1 cho đối sánh chính xác hoặc giá trị nhỏ hơn tiếp theo
• 1 cho đối sánh chính xác hoặc giá trị lớn hơn tiếp theo
• 2 cho đối sánh một phần bằng cách sử dụng các ký tự đại diện được nối với tham chiếu ô trong đối số lookup_value

Đối số search_mode tùy chọn có thể chứa bất kỳ giá trị nào trong số bốn giá trị sau:

• 1 để tìm kiếm từ đầu đến cuối, nghĩa là từ trên xuống dưới (mặc định, giống như khi không có đối số search_mode nào được chỉ định)
• -1 để tìm kiếm từ cuối đến đầu tiên, nghĩa là từ dưới lên trên
• 2 cho một tìm kiếm nhị phân theo thứ tự tăng dần
• -2 cho tìm kiếm nhị phân theo thứ tự giảm dần

Cách tốt nhất để hiểu sức mạnh và tính linh hoạt của hàm XLOOKUP mới là xem nó hoạt động trong trang tính Excel. Trong hình sau, tôi có một trang tính với bảng dữ liệu bán hàng năm 2019 đơn giản được sắp xếp theo quốc gia. Để sử dụng XLOOKUP để trả về tổng doanh số từ bảng này trong ô E4 dựa trên quốc gia bạn nhập vào ô D4 của trang tính, bạn thực hiện các bước sau:

Định vị con trỏ ô trong ô E4 của trang tính

Nhấp vào tùy chọn Tra cứu & Tham khảo trên tab Công thức, sau đó là XLOOKUP ở gần cuối trình đơn thả xuống để mở hộp thoại Đối số Hàm của nó.

Bấm vào ô D4 trong trang tính để nhập tham chiếu ô của nó vào hộp văn bản đối số Lookup_value.

Nhấn Tab để chọn hộp văn bản đối số Lookup_array, sau đó nhấp vào ô A4 và giữ phím Shift khi bạn nhấn Ctrl-mũi tên xuống để chọn A4: A8 làm phạm vi cần tìm kiếm (vì phạm vi A3: B8 được xác định là bảng dữ liệu Excel, Bảng1 [Quốc gia] xuất hiện trong hộp văn bản thay cho phạm vi A4: A8).

Nhấn Tab để chọn hộp văn bản đối số Return_array, sau đó nhấp vào ô B4 và giữ phím Shift khi bạn nhấn Ctrl-mũi tên xuống để chọn B4: B8 làm phạm vi chứa các giá trị được trả về dựa trên kết quả tìm kiếm (xuất hiện dưới dạng Bảng1 [Tổng doanh số] trong hộp văn bản).

Bấm OK để nhập công thức XLOOKUP vào ô E4.

Tạo công thức với XLOOKUP trong ô E4 trả lại doanh số bán hàng dựa trên quốc gia được nhập trong ô D4.

Excel nhập công thức XLOOKUP vào ô E4 của trang tính và trả về kết quả là 4900 vì Costa Rica hiện được nhập vào ô tra cứu D4 và như bạn có thể thấy trong bảng doanh số năm 2019, đây thực sự là tổng doanh số bán hàng cho quốc gia này.

Vì XLOOKUP hoạt động từ phải sang trái cũng như từ trái sang phải, bạn cũng có thể sử dụng chức năng này để trả về quốc gia từ bảng bán hàng này dựa trên một số liệu bán hàng cụ thể. Hình sau đây cho bạn thấy cách bạn làm điều này. Lần này, bạn tạo công thức XLOOKUP trong ô D4 và chỉ định giá trị đã nhập vào ô E4 (trong trường hợp này là 11,000) làm đối số lookup_value.

Ngoài ra, bạn nhập -1 làm đối số match_mode để ghi đè mặc định đối sánh chính xác của hàm để Excel trả về quốc gia có giá trị khớp chính xác với giá trị bán hàng được nhập trong ô tra cứu E4 hoặc quốc gia có tổng doanh số thấp hơn tiếp theo (Mexico với 10.000 đô la trong trường hợp này vì không có quốc gia nào trong bảng này có tổng doanh thu là 11.000 đô la). Nếu không chỉ định đối số match_mode cho công thức này, kết quả là Excel sẽ trả về #NA, vì không có kết quả khớp chính xác nào với 11.000 đô la trong bảng doanh số này.

Tạo công thức với XLOOKUP trong ô D4 trả về quốc gia dựa trên doanh số đã nhập trong ô E4

Bởi vì hàm XLOOKUP thoải mái như nhau khi tìm kiếm theo chiều ngang theo cột khi tìm kiếm theo chiều dọc theo hàng, bạn có thể sử dụng nó để tạo công thức thực hiện tra cứu hai chiều (thay thế việc phải tạo công thức kết hợp các hàm INDEX và MATCH như trong quá khứ). Hình dưới đây, chứa bảng lịch trình sản xuất năm 2019 cho các bộ phận, AB-100 đến AB-103 cho các tháng từ tháng 4 đến tháng 12, cho bạn biết cách thực hiện điều này.

Tạo công thức với các hàm XLOOKUP lồng nhau để trả về số lượng đơn vị được sản xuất cho một phần trong một tháng cụ thể

Trong ô B12, tôi đã tạo công thức sau:

= XLOOKUP (part_lookup, $ A $ 3: $ A $ 6, XLOOKUP (date_lookup, $ B $ 2: $ J $ 2, $ B $ 3: $ J $ 6))

Công thức này bắt đầu bằng cách xác định một hàm XLOOKUP tìm kiếm theo hàng theo chiều dọc để tìm khớp chính xác với mục nhập bộ phận được thực hiện trong ô có tên part_lookup (trong trường hợp này là ô B10) trong phạm vi ô $ A $ 3: $ A $ 6 của bảng sản xuất . Tuy nhiên, lưu ý rằng đối số return_array cho hàm LOOKUP ban đầu này tự nó là một hàm XLOOKUP thứ hai.

Hàm XLOOKUP lồng nhau thứ hai này tìm kiếm phạm vi ô $ B $ 2: $ J $ 2 theo chiều ngang theo cột để khớp chính xác với mục nhập ngày tháng được thực hiện trong ô có tên date_lookup (trong trường hợp này là ô B11). Đối số return_array cho hàm XLOOKUP lồng nhau thứ hai này là $ B $ 3: $ J $ 6, phạm vi ô của tất cả các giá trị sản xuất trong bảng.

Cách thức hoạt động của công thức này là Excel đầu tiên tính toán kết quả của hàm XLOOKUP lồng nhau thứ hai bằng cách thực hiện tìm kiếm theo chiều ngang, trong trường hợp này, trả về mảng trong phạm vi ô D3: D6 của cột Jun-19 (với các giá trị: 438, 153, 306 và 779) là kết quả của nó. Đến lượt nó, kết quả này trở thành đối số return_array cho hàm XLOOKUP ban đầu thực hiện tìm kiếm theo hàng dọc để tìm khớp chính xác với mục nhập số bộ phận được thực hiện trong ô B11 (có tên part_lookup). Bởi vì, trong ví dụ này, ô part_lookup này chứa AB-102, công thức chỉ trả về giá trị sản xuất tháng 6-19, 306, từ kết quả của hàm XLOOKUP thứ hai, tiếp theo.

Đây là bạn có nó! Cái nhìn đầu tiên về XLOOKUP, một hàm tra cứu mới mạnh mẽ, linh hoạt và khá dễ sử dụng, không chỉ có thể thực hiện tra cứu một giá trị được thực hiện bởi các hàm VLOOKUP và HLOOKUP mà còn thực hiện tra cứu giá trị hai chiều bằng cách kết hợp Hàm INDEX và MATCH cũng vậy.

* Rất tiếc, hàm XLOOKUP không tương thích ngược với các phiên bản trước của Microsoft Excel mà chỉ hỗ trợ hàm VLOOKUP và HLOOKUP hoặc tương thích với các phiên bản hiện tại chưa bao gồm nó làm một trong các hàm tra cứu của chúng, chẳng hạn như Excel 2019 và Excel Online . Điều này có nghĩa là nếu bạn chia sẻ sổ làm việc chứa công thức XLOOKUP với đồng nghiệp hoặc khách hàng đang sử dụng phiên bản Excel không bao gồm hàm tra cứu mới này, tất cả các công thức này sẽ trả về lỗi #NAME? giá trị lỗi khi họ mở trang tính của nó.