Hướng dẫn XLOOKUP trong Excel

Hướng dẫn XLOOKUP trong Excel

Table of Contents:

1️⃣ Giới thiệu về hàm XLOOKUP trong Microsoft Excel 2️⃣ Cách sử dụng cơ bản của XLOOKUP 3️⃣ XLOOKUP và VLOOKUP: So sánh và hướng dẫn sử dụng 4️⃣ XLOOKUP với bảng dọc 5️⃣ XLOOKUP với bảng ngang 6️⃣ XLOOKUP với các trang tính khác nhau 7️⃣ Lấy nhiều giá trị trả về từ XLOOKUP 8️⃣ XLOOKUP với nhiều tiêu chí tìm kiếm 9️⃣ Các chế độ khớp và chế độ tìm kiếm trong XLOOKUP 🔟 Sử dụng XLOOKUP trong lưới dữ liệu

1️⃣ Giới thiệu về hàm XLOOKUP trong Microsoft Excel

Hôm nay, tôi muốn chỉ cho bạn cách sử dụng hàm XLOOKUP trong Microsoft Excel. Giống như tên gọi của nó, với XLOOKUP, bạn có thể tìm kiếm các giá trị. Ví dụ, hãy nói tôi có một chiếc cookie socola chip là cookie yêu thích của tôi, và tôi muốn biết chiếc cookie này giá bao nhiêu. Tôi có một bảng với tất cả các mức giá của các loại cookie khác nhau. Tôi có thể tìm giá của chiếc socola chip. Tôi cũng có thể thay đổi loại cookie và lấy giá cho cookie đường. Tôi biết, tôi biết, những chiếc cookie này đắt quá, nhưng hey, chúng ngon lắm!

2️⃣ Cách sử dụng cơ bản của XLOOKUP

Chúng ta sẽ bắt đầu với một ví dụ đơn giản nhất để hiểu cơ bản về cách XLOOKUP hoạt động, sau đó chúng ta sẽ xem xét một số khả năng tiên tiến hơn. XLOOKUP hiện chỉ có sẵn cho các thuê bao Microsoft 365. Nếu bạn không có nó, tôi cũng đã đính kèm một video để chỉ cho bạn cách sử dụng VLOOKUP và HLOOKUP. Và bây giờ, hãy cùng xem xét hàm XLOOKUP.

Hãy bắt đầu với ví dụ cơ bản chỉ để bạn hiểu cách cơ bản của XLOOKUP. Nếu bạn muốn làm theo, tôi đã đính kèm một liên kết đến bảng tính này ở phần mô tả phía dưới. Bên phải, tôi có một chiếc cookie socola chip, và tôi muốn biết nó giá bao nhiêu. Bên trái, tôi có một bảng với các mức giá và loại cookie. Tôi muốn tìm kiếm loại cookie socola chip trong bảng này. Vì vậy, tôi muốn tìm kiếm tên cookie, rồi tôi muốn lấy giá này trở lại ô này. Để làm điều đó, chúng ta sẽ sử dụng, bạn đoán đúng rồi, hàm XLOOKUP. Cách dễ nhất để nhập một hàm là sử dụng trợ giúp chèn hàm. Hãy đi vào góc trái trên cùng và nhấp vào biểu tượng này. Trong cửa sổ này, hãy tìm kiếm hàm XLOOKUP và nhấp vào "go". Bạn sẽ thấy XLOOKUP, nhấp vào đó. Điều này mở ra các đối số hàm và bên trái, bạn sẽ thấy rằng một số đối số được in đậm và một số không được in đậm. Vậy, sự khác biệt là gì? Những đối số đậm là bắt buộc và những đối số không được in đậm là tùy chọn. Trong ví dụ cơ bản này, chúng ta chỉ tập trung vào ba đối số đầu tiên. Bạn chỉ cần những cái này để thực hiện tìm kiếm đơn giản. Vậy, hãy tìm giá cho một chiếc cookie socola chip. Lên trên cùng, nó yêu cầu bạn giá trị tìm kiếm, và một lần nữa, tôi muốn biết giá của một chiếc cookie socola chip. Vậy đây là điều chúng ta đang tìm kiếm. Tiếp theo, nó yêu cầu bạn cho mảng tìm kiếm. Vâng, tôi đang tìm kiếm cookie socola chip trong bảng này. Và đây, tôi sẽ chọn cột loại cookie là mảng tìm kiếm. Khi nó tìm thấy cookie socola chip, tôi muốn lấy giá trị này. Vì vậy, mảng trả về là giá. Tôi sẽ làm nổi bật cột này. Vậy đây, khi nó tìm thấy cookie socola chip, nó sẽ trả lại giá trị này từ cùng một hàng. Bên dưới, chúng ta có thể thấy rằng giá là năm. Và một lần nữa, tôi đã điền đầy đủ các trường bắt buộc. Tôi sẽ nhấp vào "OK". Và ở đây lại, chúng ta có thể thấy rằng giá là 5 đô la. Hãy nhìn vào đó. Bạn đã thành công trong việc tìm kiếm giá trị đầu tiên của mình bằng XLOOKUP. Phải không dễ dàng? Như một điểm cộng, bạn cũng đã biết giá của chiếc cookie của chúng tôi tại công ty Kevin Cookie là vô cùng đắt đỏ. Trong bảng tính Excel của chúng ta, ở đây bạn có thể thấy Công thức cho XLOOKUP. Bạn có thể gõ trực tiếp như thế này, hoặc bạn có thể sử dụng trợ giúp chức năng, giống như chúng ta đã làm. Cá nhân tôi thích trợ giúp chức năng vì tôi nghĩ nó làm cho việc này dễ dàng hơn. Trong công thức của chúng ta, vì tôi tham chiếu đến loại cookie ngay đây, tôi có thể thay đổi tên cookie. Ví dụ, tôi gõ vào cookie đường và tôi muốn biết giá cho nó. Khi tôi nhấn enter, giá tự động cập nhật để phản ánh giá cho một chiếc cookie đường. Bây giờ hãy nói thay vì tìm kiếm cookie đường, tôi gõ vào "Siêu Cookie ngon miệng tuyệt vời của Kevin". Nghe có vẻ thật tuyệt, nhưng bạn có thể thấy rằng loại cookie này không tồn tại trong bảng này. Và tôi nhận được thông báo lỗi #N/A này. Để hiển thị thông báo lỗi tốt hơn, hãy mở trợ giúp chèn số một lần nữa. Tôi sẽ nhấp vào biểu tượng này và ở đây một lần nữa, chúng ta có thể thấy tất cả các đối số hàm. Điều này đưa chúng ta đến tham số tùy chọn đầu tiên và đó là "if not found" (nếu không tìm thấy). Vì vậy, nếu có một loại cookie không tồn tại, chúng tôi có thể cung cấp một thông báo thân thiện hơn cho người dùng. Ở đây, tôi sẽ chèn một dấu ngoặc kép. Tôi sẽ gõ "không tồn tại" và sau đó tôi đóng dấu ngoặc kép và nhấp vào "OK". Vậy nên, khi chúng ta tìm kiếm "Siêu Cookie ngon miệng tuyệt vời của Kevin", rõ ràng không có điều này. XLOOKUP hoạt động tuyệt vời khi bạn có một danh sách dọc của dữ liệu, nhưng khi bạn có một danh sách ngang thì sao? Ở đây, trên bảng tính tiếp theo có tiêu đề "Ngang", XLOOKUP hoạt động chính xác như với dữ liệu được trình bày theo chiều ngang. Đúng đây, tôi sẽ kích vào ô giá, tôi sẽ chọn trợ giúp chức năng, và hãy click vào XLOOKUP. Ở đây, một lần nữa tôi sẽ tìm kiếm cookie socola chip. Ở đây tôi muốn tìm nó trong mảng dữ liệu này, và một lần nữa tôi muốn lấy giá trị trở lại. Ở đây, tôi sẽ nhấp vào "OK" và đây cũng là giá 5. Ngoại lệ với danh sách dữ liệu ngang, bạn cũng có thể sử dụng XLOOKUP trên nhiều bảng tính khác nhau. Bạn có thể đã ghi nhớ giá của chiếc cookie socola chip của chúng tôi, nhưng nhớ rằng bạn cũng có thể sử dụng XLOOKUP trên các bảng tính khác nhau. Ở đây, tôi sẽ mở trợ giúp chức năng một lần nữa, nhấp vào XLOOKUP, và ở đây tôi muốn tìm kiếm cookie socola chip. Đối với mảng tìm kiếm, không có bảng trên trang tính này. Ở đây, tôi sẽ nhấp vào bảng mức giá, tôi sẽ chọn cookie socola chip trong cột này, và ở đây tôi muốn nhấp vào "OK". Do đó, ở đây cũng có thể lấy giá trở lại, ngay cả khi bảng tôi tìm kiếm giá của nằm trên một trang tính riêng biệt. Một trong những điều tuyệt vời về XLOOKUP không chỉ có thể trả về một giá trị, bạn cũng có thể cho XLOOKUP trả về nhiều giá trị. Ở đây trên trang tính dữ liệu trả về, không chỉ tôi muốn tìm kiếm cookie sô-cô-la chip và lấy giá trở lại, mà tôi cũng muốn lấy lại giá thành, và một lần nữa XLOOKUP có thể thực hiện điều này. Hãy nhấp vào trợ giúp chức năng, tôi sẽ nhấp vào XLOOKUP, và một lần nữa, chúng ta đang tìm kiếm cookie socola chip. Trong mảng tìm kiếm, giống như chúng ta đã làm mãi, ở đây tôi sẽ chọn các loại cookie. Đối với mảng trả về bây giờ, không chỉ muốn lấy giá trở lại mà cũng muốn lấy giá thành. Vậy làm thế nào chúng ta có thể làm điều đó? Vâng, ở đây tôi chỉ cần chọn cả hai cột này, và sau đó tôi sẽ nhấp vào "OK". Và nhìn vào đây, ở đây nó cho tôi giá trị trở lại cho mỗi cookie là 5 đô la, và nó tràn sang ô tiếp theo, và nó cũng cho tôi giá thành lại. Khi bạn nhận được nhiều giá trị trả về, bạn có thể lồng nó trong một hàm khác. Điều đó có nghĩa là gì? Chúng ta hãy kiểm tra điều đó. Trước khi chúng ta chuyển sang trang tính kế tiếp, ở đây tôi sẽ sao chép công thức XLOOKUP mà chúng ta đã nhập và tôi sẽ chép nó ngay vào khoảng dấu bằng. Tiếp theo, chúng ta chuyển sang trang tính các hàm kết hợp. Trong các hàm kết hợp này, ở phía trên góc phải trên cùng, tôi muốn biết chúng ta kiếm được bao nhiêu lợi nhuận cho mỗi lần bán cookie socola chip. Để tính toán lợi nhuận, tôi sẽ lấy doanh thu, tức là 5, và sau đó tôi trừ đi giá thành mỗi cookie, tức là 2. Vì vậy, lợi nhuận sẽ là 3. Tôi có thể sử dụng hàm SUM. Tôi sẽ cộng tổng doanh thu và giá thành, và điều đó sẽ cho tôi biết lợi nhuận là bao nhiêu. Ngay ở trên đây, hãy nhấp vào trợ giúp chức năng. Ở đây tôi sẽ gõ SUM, nhấp vào "go", và để nhấp vào hàm SUM. Trong các đối số hàm SUM, nó hỏi tôi cái gì tôi muốn cộng tổng. Nhớ rằng chúng ta đã sao chép công thức XLOOKUP từ trang tính trước. Ở đây, tôi chỉ cần dán nó vào đây, và nó sẽ cộng tổng 5 và cộng tổng giá thành âm 2, và tôi có thể nhấp vào "OK", và ở đây tôi thấy rằng lợi nhuận của tôi là 3. Điều thú vị ở đây là bạn có thể lồng XLOOKUP của mình trong các hàm khác, và trong trường hợp này, tôi đang cộng tổng tất cả các giá trị khác nhau được trả về bởi XLOOKUP. Với XLOOKUP, bạn cũng có thể tìm kiếm dựa trên một hay nhiều tiêu chí. Hãy xem xét điều đó. Trên trang tính tiếp theo có tiêu đề "Multiple Lookups", tôi muốn biết chiếc cookie socola chip cổ điển của chúng tôi giá bao nhiêu. Và đúng đấy, giờ đây chúng tôi có một cookie socola chip cổ điển bên cạnh một loại cookie socola chip mới. Sự khác biệt là gì? Vâng, tôi không thể nói cho bạn biết, nhưng vì lý do nào đó, chúng tôi có thể kiếm được nhiều hơn từ chiếc cookie socola chip mới này. Có thể giống như "cái Coke mới". Để tìm kiếm theo nhiều tiêu chí, hãy nhấp vào ô giá một lần nữa. Hãy mở trợ giúp chức năng. Hãy nhấp vào XLOOKUP, và ở đây cho giá trị tìm kiếm, trước tiên hãy chọn "socola chip". Sau đó, tôi sẽ chèn ký hiệu '&', sau đó tôi chọn "cổ điển". Vì vậy, chúng ta đang tìm kiếm hai giá trị khác nhau. Trong mảng tìm kiếm, chúng ta làm điều tương tự. Ở đây, tôi sẽ chọn cột này. Tôi chèn dấu ampersand và sau đó tôi chọn cột này. Và đối với mảng trả về, tôi sẽ chọn giá mỗi cookie và sau đó tôi sẽ nhấp vào "OK". Và ở đây chúng ta có thể thấy rằng chiếc cookie socola chip cổ điển có giá là 5. Trên trang tính tiếp theo có tiêu đề "Match Mode", chúng ta sẽ khám phá một số tham số tùy chọn. Cho đến nay, chúng ta chỉ làm một khớp chính xác. Vì vậy, chúng ta đã tìm chiếc cookie socola chip và trả về giá là 5. Và nếu chúng ta không tìm thấy chiếc cookie socola chip, chúng ta trả về #N/A. Nhưng nếu một khách hàng đến cửa hàng và khách hàng nói: Tôi có 5,50 đô la để mua một chiếc cookie. Bạn gợi ý gì? Vâng, tôi nên gợi ý chiếc cookie socola chip, vì cá nhân tôi nghĩ rằng đó là loại cookie ngon nhất, nhưng chúng ta muốn tối đa hóa doanh thu của mình. Vì vậy, trong trường hợp này, tôi sẽ khuyên bạn nên chọn cookie yến mạch sultana với giá 5,45 đô la. Để khuyến nghị chiếc cookie yến mạch sultana, tôi có thể sử dụng các chế độ khớp khác nhau này. Vì vậy, hãy thử thực hiện nó. Ở đây tôi sẽ nhấp vào ô E2. Tôi sẽ nhấp vào trợ giúp chức năng. Một lần nữa, chúng ta sẽ sử dụng XLOOKUP. Ở đây cho giá trị tìm kiếm, tôi muốn tìm giá trên một chiếc cookie. Và ở đây tôi sẽ tìm kiếm trong cột giá. Và ở đây có tùy chọn chế độ khớp. Và tôi muốn khuyến nghị mục nhỏ tiếp theo. Vì vậy, tôi sẽ nhập số âm một. Ở đây, tôi có thể thấy rằng nó sẽ khuyến nghị cookie yến mạch sultana. Tôi sẽ nhấp vào "OK". Và ở đây chúng ta có khuyến nghị của chúng tôi cho khách hàng. Ngoài chế độ khớp, bạn cũng có một tham số tùy chọn khác được gọi là chế độ tìm kiếm. Chế độ tìm kiếm là thứ tự bạn muốn tìm kiếm. Và mặc định, bạn tìm kiếm từ trên xuống dưới. Vì vậy, giả sử tôi muốn biết Lola, một trong những khách hàng của chúng tôi, đơn hàng cuối cùng của cô ấy là khi nào. Theo mặc định, nó chỉ di chuyển từ đầu danh sách đến cuối danh sách. Nhưng ở đây, nếu tôi bắt đầu từ đầu danh sách, đây là đơn hàng cũ nhất và đó không nhất thiết là đơn hàng cuối cùng của cô ấy. Ở đây, hãy nhấp vào trợ giúp chức năng. Tôi đã điền đầy đủ hàm XLOOKUP tiêu chuẩn. Nhưng ở đây, nếu tôi cuộn xuống, tôi thấy một tùy chọn cho chế độ tìm kiếm. Và ở đây, tôi có thể thực hiện một tìm kiếm ngược bắt đầu từ mục cuối cùng. Vì vậy, tôi sẽ nhập số âm một. Tôi cũng có tùy chọn sử dụng tìm kiếm nhị phân, nhưng để làm điều đó, tôi phải sắp xếp nó theo thứ tự tăng dần hoặc giảm dần. Tôi sẽ nhập số âm một, nhấp vào "OK", và ở đây tôi thấy rằng đơn hàng cuối cùng của Lola là vào ngày 13/7. Vì vậy, đó là một tùy chọn khác bạn có thể tùy chỉnh. Chúng ta đã có tiến bộ tuyệt vời với XLOOKUP. Và cuối cùng, tôi muốn để lại cho bạn có lẽ là kịch bản phức tạp nhất và nâng cao nhất. Bạn có thể sử dụng XLOOKUP để tìm kiếm giá trị trong một lưới dữ liệu. Để làm điều này, chúng ta sẽ lồng một XLOOKUP trong một XLOOKUP. Tôi biết rằng nó nghe có vẻ phức tạp, nhưng chúng ta sẽ đi từng bước một. Điều này giờ đây đưa chúng ta đến trang tính cuối cùng có tên XLOOKUP lồng nhau. Và đây là trận đấu cuối cùng của XLOOKUP. Bạn đã đến đích từ xa như vậy, hãy cùng trải qua điều này. Chúng ta sẽ sử dụng XLOOKUP để cho chúng ta biết chúng ta kiếm được bao nhiêu tiền hoặc doanh thu từ việc bán cookie socola chip trong năm 2021. Và ở đây bạn có thể thấy rằng đó là một lưới. Chúng tôi có loại cookie và chúng tôi cũng có các năm và chúng tôi muốn chọn ra số tiền doanh thu từ lưới dữ liệu này. Nó trông rất khó, nhưng chúng ta sẽ chia nhỏ nó và chúng ta sẽ sử dụng hai XLOOKUP riêng biệt để làm điều này. Trước tiên, hãy trả lại doanh thu cho năm được chọn. Tôi sẽ nhấp vào ô F7, nhấp vào trợ giúp chức năng và ở đây một lần nữa, hãy chọn XLOOKUP. Đối với giá trị tìm kiếm, tôi sẽ chọn năm. Với mảng tìm kiếm, tôi sẽ chọn tất cả các năm khác nhau. Và với mảng trả về, ở đây tôi sẽ chọn tất cả các số tiền doanh thu khác nhau rồi tôi sẽ nhấp vào "OK". Và ở đây chúng ta thấy tất cả các doanh thu cho năm 2021. Tiếp theo, tôi muốn tìm doanh thu cho cookie socola chip. Tôi muốn xem danh sách ở đây và khi tìm thấy cookie socola chip, tôi muốn tìm kết quả này. Vì vậy, tôi muốn tìm kiếm theo đầu ra của hàm XLOOKUP trước đó này. Hãy sao chép điều đó và chúng ta sẽ chèn nó vào XLOOKUP khác. Hãy nhấp vào ô C4. Hãy quay lại trợ giúp chức năng và một lần nữa, chúng ta sẽ sử dụng một hàm XLOOKUP. Lần này cho giá trị tìm kiếm, tôi muốn tìm kiếm cookie socola chip. Đối với mảng tìm kiếm, tôi sẽ chọn tất cả các loại cookie khác nhau. Và đối với mảng trả về, tôi có thể chọn bộ này và sau đó tôi sẽ nhấp vào "OK". Và ở đây chúng ta có thể thấy rằng doanh thu trở lại là hơn 8 triệu. Nhưng thay vì chỉ trỏ vào bộ này cụ thể, thay vào đó, tôi có thể chèn hàm XLOOKUP trực tiếp vào đây. Ở đây tôi sẽ dán hàm XLOOKUP mà chúng ta đã nhập trước đó. Vì vậy, bây giờ tôi có một XLOOKUP lồng trong một XLOOKUP. Tôi nhấn enter và kết quả không thay đổi chút nào. Đó vẫn là chính xác như trước, chỉ khác là lần này nó hiệu quả hơn một chút và chúng ta có thể xóa cái XLOOKUP này. Vì chúng ta đã có công thức, ở đây tôi có thể thay đổi năm hoặc loại cookie và ở đây chúng ta thấy nó tự động cập nhật đến giá trị chính xác. Vì vậy, bạn có thể thấy nó được cập nhật. Vì vậy, bây giờ chúng ta thành công trong việc tìm kiếm qua một lưới dữ liệu. Tốt lắm, bạn đã học hai điều rất quan trọng hôm nay. Bạn hiện đã biết cách sử dụng XLOOKUP. Bạn cũng đã biết cách tìm kiếm giá của các loại cookie. Vì thế, lần sau khi bạn đặt một đơn hàng tại KevinCookieCompany.com, bạn sẽ biết chính xác nó giá bao nhiêu. Để xem thêm các video hướng dẫn như video này, hãy xem danh sách phát ở trên. Xin vui lòng xem xét việc đăng ký và tôi sẽ gặp bạn lần sau."""