Hàm VLOOKUP trong Excel thực sự hữu ích khi tìm kiếm một giá trị nhất định trên cơ sở dữ liệu. Nếu bạn đã biết cách sử dụng Vlookup cơ bản, hãy đọc bài viết này để tự tin hơn khi ứng dụng Vlookup nâng cao nhé.
Trước tiên, hãy ôn lại cú pháp hàm Vlookup nhé
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM,[RANGE_LOOKUP])
Trong đó:
- – Lookup_value (bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.
- – Table_array (bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.
- – Row_index_num (bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.
- – Range_lookup (tuỳ chọn): Một giá trị logic (Boolean) cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.
-
- + Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.
- + Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ trả về lỗi #N/A
Hàm VLOOKUP hai điều kiện
Hàm Vlookup thường chỉ tìm kiếm được với điều kiện dò tìm là 1 ô tham chiếu, một giá trị, hoặc một chuỗi văn bản. Vậy nếu điều kiện dò tìm nằm ở nhiều ô, nhiều giá trị khác hoặc từ 2 chuỗi văn bản trở lên thì chắc chắn ta không thể sử dụng hàm Vlookup thông thường được. Tham khảo ngay ví dụ dưới đây
Giả sử bạn có danh sách các đơn hàng và muốn tìm số lượng (Qty.) dựa trên 2 tiêu chí Customer name và Product. Tuy nhiên, yếu tố phức tạp là mỗi khách hàng đặt hàng nhiều sản phẩm
Bạn có thể thêm cột phụ nối các giá trị từ hai cột tra cứu (Customer name và Product). Điều quan trọng là cột phụ phải là cột ngoài cùng bên trái trong mảng bảng vì đó là nơi hàm VLOOKUP trong Excel luôn tìm kiếm giá trị tra cứu.
Vì vậy, hãy thêm một cột vào bên trái bảng của bạn và sao chép công thức bên dưới qua cột đó. Điều này sẽ điền vào cột thêm vào với các giá trị từ cột B và C (ký tự khoảng trắng được nối ở giữa để dễ đọc hơn)
Hàm VLOOKUP nhiều điều kiện
Về lý thuyết, bạn có thể sử dụng phương pháp trên với Vlookup nhiều hơn hai tiêu chí. Tuy nhiên, có một vài lưu ý. Thứ nhất, giá trị tra cứu được giới hạn trong 255 ký tự và thứ hai, thiết kế của trang tính có thể không cho phép thêm cột trợ giúp.
May mắn thay, Microsoft Excel thường cung cấp nhiều cách để làm điều tương tự. Để Vlookup nhiều điều kiện, bạn có thể sử dụng kết hợp INDEX MATCH hoặc hàm XLOOKUP được giới thiệu gần đây trong Office 365.
Ví dụ: để tra cứu dựa trên 3 giá trị khác nhau (Ngày, Tên khách hàng và Sản phẩm ), hãy sử dụng một trong các công thức sau:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Ở đây:
- G1 là tiêu chí 1 (ngày)
- G2 là tiêu chí 2 (tên khách hàng)
- G3 là tiêu chí 3 (sản phẩm)
- A2: A11 là phạm vi tra cứu 1 (ngày tháng)
- B2: B11 là phạm vi tra cứu 2 (tên khách hàng)
- C2: C11 là phạm vi tra cứu 3 (sản phẩm)
- D2: D11 là phạm vi trả về (số lượng)
Cách VLOOKUP và trả về nhiều giá trị trong Excel
Hàm VLOOKUP trong Excel được thiết kế để chỉ trả về một kết quả phù hợp. Có cách nào để Vlookup nhiều trường hợp không? Hãy sử dụng kết hợp một số hàm như INDEX, SMALL và ROW.
Ví dụ: hàm bên dưới có thể tìm thấy tất cả các lần xuất hiện của giá trị tra cứu F2 trong phạm vi tra cứu B2: B16 và trả về nhiều kết quả phù hợp từ cột C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Có 2 cách để nhập công thức vào trang tính:
- Nhập công thức vào ô đầu tiên, nhấn Ctrl + Shift + Enter, rồi kéo nó xuống một vài ô nữa.
- Chọn một số ô liền kề trong một cột duy nhất (F1: F11 trong ảnh chụp màn hình bên dưới), nhập công thức và nhấn Ctrl + Shift + Enter để hoàn thành.
Tra cứu hai chiều với VLOOKUP (Vlookup trong hàng và cột)
Đôi khi, bạn có thể cần thực hiện tra cứu 2 chiều, nghĩa là Vlookup cả hàng và cột cùng một lúc. Giả sử, nếu bạn có phạm vi dữ liệu sau và bây giờ, bạn có thể cần nhận giá trị cho một sản phẩm cụ thể trong một quý cụ thể. Phần này sẽ giới thiệu một số công thức để xử lý công việc này trong Excel.
Công thức 1: Sử dụng hàm VLOOKUP và MATCH
Trong Excel, bạn có thể sử dụng kết hợp các hàm Vlookup và MATCH để tra cứu hai chiều, vui lòng áp dụng công thức sau vào một ô trống, rồi nhấn Đi vào phím để nhận kết quả.
=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)
Lưu ý: Trong công thức trên:
- – H1: giá trị tra cứu trong cột mà bạn muốn lấy giá trị tương ứng dựa vào đó;
- – A2: E6: phạm vi dữ liệu bao gồm tiêu đề hàng;
- – H2: giá trị tra cứu trong hàng mà bạn muốn lấy giá trị tương ứng dựa trên;
- – A1: E1: các ô của tiêu đề cột.
Công thức 2: Sử dụng hàm INDEX và MATCH
Đây là một công thức khác cũng có thể giúp bạn thực hiện tra cứu 2 chiều, vui lòng áp dụng công thức dưới đây, sau đó nhấn Đi vào chìa khóa để nhận được kết quả bạn cần.
=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))
Lưu ý: Trong công thức trên:
- – B2: E6: phạm vi dữ liệu để trả về mục phù hợp từ;
- – H1: giá trị tra cứu trong cột mà bạn muốn lấy giá trị tương ứng dựa vào đó;
- – A2: A6: tiêu đề hàng chứa sản phẩm bạn muốn tìm.
- – H2: giá trị tra cứu trong hàng mà bạn muốn lấy giá trị tương ứng dựa trên;
- – B1: E1: tiêu đề cột chứa phần tư bạn muốn tìm.
Cách thực hiện nhiều VLOOKUP trong Excel (Vlookup lồng nhau)
Đôi khi có thể xảy ra trường hợp bảng chính và bảng tra cứu của bạn không có chung một cột để thực hiện Vlookup giữa hai bảng. Tuy nhiên, tồn tại một bảng khác không chứa thông tin bạn đang tìm kiếm nhưng có một cột chung với bảng chính và một cột chung khác với bảng tra cứu.
Mục tiêu là sao chép Price (Giá) vào bảng chính dựa trên Item ID. Vấn đề là bảng chứa giá không có Item ID, có nghĩa là chúng ta sẽ phải thực hiện hai Vlookup trong một công thức.
Để thuận tiện, trước tiên hãy tạo một vài phạm vi được đặt tên:
- – Bảng tra cứu 1 có tên là Products (D3:E3).
- – Bảng tra cứu 2 có tên là Prices (G3:H3).
Các bảng có thể nằm trong các trang tính giống nhau hoặc khác nhau.
Và bây giờ, chúng ta sẽ thực hiện cái gọi là Vlookup kép, hay còn gọi là Vlookup lồng nhau .
Đầu tiên, hãy tạo công thức VLOOKUP để tìm tên sản phẩm trong bảng Tra cứu 1 (có tên Products) dựa trên ID mặt hàng (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Tiếp theo, đặt công thức trên vào đối số lookup_value của một hàm VLOOKUP khác để kéo giá từ Bảng tra cứu 2 (có tên là Prices) dựa trên tên sản phẩm được trả về bởi hàm VLOOKUP lồng nhau:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Xem thêm