Vlookup Excel Nâng Cao | Hàm Vlookup Hai Điều Kiện

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.

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)

6 cách sử dụng hàm VLOOKUP thông minh trong Excel (cơ bản đến nâng cao)

Một số lưu ý khi sử dụng hàm Vlookup nâng cao.

4.Sử dụng địa chỉ tuyệt đối khi dùng hàm Vlookup nâng cao.

Trong Excel có 3 loại địa chỉ:

Địa chỉ tương đối: Là địa chỉ bị thay đổi tương ứng với mỗi dòng và cột khi chúng ta thực hiện sao chép công thức. (VD: B5 là địa chỉ của hàng 5 cột B).
Địa chỉ tuyệt đối: Là địa chỉ được cố định lại, không thay đổi khi ta copy công thức. (VD: $A$1- địa chỉ tuyệt đối của 1 ô, $B$17:$C$20 – địa chỉ tuyệt đối của 1 vùng)
- Để tạo địa chỉ tuyệt đối, thì bạn nhấn phím F4, lúc này sẽ có dấu đô la ($) ở trước chỉ số cột và dòng.
- Tóm lại nếu là địa chỉ tuyệt đối thì bạn thấy có dấu đô la ($) trước chỉ số cột và dòng.
Địa chỉ hỗn hợp: Địa chỉ hỗn hợp là địa chỉ chỉ cố định dòng hoặc cột mà thôi.
- Cố định cột: Ví dụ: $A1, thì bạn thấy chỉ số cột được cố định, còn chỉ số dòng không được cố định.
- Cố định dòng: Ví dụ: A$1 thì bạn thấy chỉ số cột không được cố định, còn chỉ số dòng cố định.

Khi sử dùng hàm Vlookup trong Excel bạn thường phải tìm kiếm cho cả cột nên việc copy công thức là không tránh khỏi. Lúc này bạn cần lưu ý để địa chỉ của vùng tìm kiếm là địa chỉ tuyệt đối để khi ta copy công thức cho những hàng khác thì vùng tìm kiếm của ta không bị thay đổi.

4.Hàm Vlookup nâng cao trả về giá trị đầu tiền được tìm thấy.

Nếu cột ngoài cùng bên trái của bảng chứa các giá trị trùng lặp nhau thì sẽ lấy giá trị đầu tiên được tìm thấy. Ví dụ, hãy xem hình minh họa bên dưới.

Tìm kiếm từ phải qua trái trong Excel

Hình 8: Hàm Vlookup nâng cao.

Giải thích: Hàm VLOOKUP trả về mã vùng của Hà Nội là 30, không phải là 31 vì 30 là giá trị đầu tiên được tìm thấy.

4.Hàm Vlookup nâng cao không phân biệt chữ hoa chữ thường.

Thực hiện tra cứu không phân biệt chữ hoa chữ thường. Ví dụ, ở bên dưới tra cứu NGUYỄN HUY (ô G4) ở cột ngoài cùng bên trái của bảng.

Lưu ý khi sử dụng hàm Vlookup nâng cao

Hình 9: Hàm Vlookup nâng cao.

Giải thích: Hàm VLOOKUP không phân biệt chữ hoa chữ thường nên nó sẽ tra cứu NGUYỄN HUY hoặc Nguyễn Huy hoặc nguyễn huy… Kết quả là, hàm VLOOKUP trả về tiền quê của Nguyễn Huy Tưởng (trường hợp đầu tiên).

Tổng kết.

Vlookup là một hàm cơ bản trong Excel hỗ trợ rất mạnh trong việc thống kê và dò tìm dữ liệu nhưng nếu chỉ dừng ở mức cơ bản thì nhiều trường hợp với những yêu cầu thực tế thì không phải lúc nào hàm Vlookup cùng giải quyết được. Trên đây là hướng dẫn chi tiết cách sử dụng hàm Vlookup nâng cao để tìm kiếm nhiều điều kiện, dò tìm từ phải quả trái kèm theo là một số ví dụ cụ thể hi vọng giúp ích cho các bạn. Chúc các bạn thành công!

Gợi ý học tập mở rộng.

Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản

Các bài viết liên quan:

Hàm VLOOKUP 2 điều kiện trong Excel Hàm VLOOKUP nhiều điều kiện trong Excel Hàm VLOOKUP kết hợp hàm IF trong Excel Hàm VLOOKUP kết hợp hàm SUM và SUMIF Hàm VLOOKUP ngược và cách dùng hàm VLOOKUP ngược trong Excel Hàm VLOOKUP bị lỗi #N/A, nguyên nhân, cách khắc phục. Hàm VLOOKUP trong Google Sheet

CÁCH SỬ DỤNG HÀM VLOOKUP TỪ CƠ BẢN – NÂNG CAO

Cách sử dụng hàm VLOOKUP từ cơ bản – Nâng cao: Định nghĩa, Cú pháp, Ví dụ và cách lồng với các hàm Excel khác.

Đào tạo Excel doanh nghiệp

& Thiết kế File Excel dễ sử dụng

Cú pháp hàm vlookup trong excel.

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 Vlookupcủ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 - từ cơ bản đến nâng cao — Hàm Vlookup – từ cơ bản đến nâng cao

CÚ PHÁP

= Vlookup (lookup_value, table_array, col_index_num, [range_lookup])

Diễn giải cú pháp:

– Lookup_value: Điều kiện tìm kiếm

– Table_array: Vùng tìm kiếm

– Col_index_num: Số thứ tự cột chứa giá trị cần tìm

– [Range_lookup]: Kiểu tìm kiếm

Có 2 kiểu tìm kiếm:

– 0 hoặc false: Chính xác 100%

– 1 hoặc true: Tìm kiếm tương đối

Trong chương trình Vlookup Level 2 bạn sẽ được học về kiểu tìm kiếm tương đối.

Trên thực tế, >90% các yêu cầu trong thực tế, ta chỉ cần dùng tới kiểu tìm kiếm chính xác.

QUAN TRỌNG

Trong excel ta thường chỉ cần viết công thức cho ô đầu tiên, còn lại là copy paste công thức cho các ô còn lại.

Đó là lý do vì sao, những ai giỏi Excel lại làm việc Nhanh – Hiệu quả – Chính xác như vậy.

Trong ví dụ này cũng vậy.

– Bạn chỉ cần copy công thức tại ô D3 vừa viết.

– Sau đó copy xuống các ô phía dưới D4:D12, là bạn có thể tìm được phụ cấp chức vụ cho rất nhiều người trong nháy mắt.

Hàm XLOOKUP trong Excel (cơ bản đến nâng cao)

BẠN CẦN HỖ TRỢ ?

Liên hệ Webkynang bằng email/ cửa sổ chát

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 nâng cao trong excel 2010 | Học excel cơ bản

Chức năng của hàm Vlookup nâng cao.

Trong Excel chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm Vlookup là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước.

Hàm Vlookup nâng cao giúp ta tìm kiếm với nhiều điều kiện, trên nhiều trang sheet và từ phải qua trái hoặc từ trái qua phải… một cách dễ dàng bằng cách kết hợp thêm một số hàm khác.

VÍ DỤ

Như bạn thấy trong hình phía dưới. Ta áp dụng hàm Vlookup để tìm được Phụ cấp chức vụ khi biết Chức vụ.

D3 = Vlookup(C3, $H$3:$I$7, 2, 0)

Diễn giải hàm tìm kiếm:

– Điều kiện tìm kiếm (ô C3): GĐ

– Vùng tìm kiếm (vùng $H$3:$I$7) : Bảng phụ cấp

– Số thứ tự cột chứa giá trị cần tìm: 2. (Trong bảng phụ cấp thì cột số tiền phụ cấp có thứ 2 là 2 từ cột đầu tiên của vùng tìm kiếm).

– Kiểu tìm kiếm: 0 – đây là kiểu tìm kiếm chính xác 100%

Vlookup nâng cao - Tìm kiếm dữ liệu ở nhiều sheet khác nhau — Vlookup nâng cao – Tìm kiếm dữ liệu ở nhiều sheet khác nhau

Công thức hàm Vlookup nâng cao

Để giải quyết vấn đề trên, bạn sử dụng công thức:

=VLOOKUP (LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP] )

Trong đó:

LOOKUP_VALUE: Giá trị bắt buộc cần tìm, có thể là ô tham chiếu, một giá trị hoặc một chuỗi văn bản.

TABLE_ARRAY: 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 và được đặt tên hoặc bảng tính. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên (bắt buộc)

COL_INDEX_NUM: Số thứ tự của cột chứa kết quả trả về trong Table_array.

[RANGE_LOOKUP]: Giá trị của hàm Vlookup trả kết quả về chính xác hay tương đối

Ví dụ sử dụng hàm Vlookup nâng cao trong Excel.

3.Sử dụng hàm Vlookup cơ bản.

Ví dụ: Giả sử, bạn có một bảng dữ liệu nhân viên, lưu trữ mã nhân viên, họ tên, quê quán. Một bảng khác có 2 cột là mã nhân viên và quê quán. Giờ bạn muốn điền thông tin quê quán cho từng nhân viên ở bảng 2 thì phải làm như thế nào?

Hàm Vlookup nâng cao

Hình 1: Hàm Vlookup nâng cao.

Để điền thông tin quê quán cho nhân viên, tại ô G4, ta nhập công thức dò tìm chính xác như sau: =VLOOKUP(F4,$B$4:$D$10,3,0)

Trong đó:

F4: Là giá trị cần đối chiếu.
$B$4:$D$10: Là bảng dò tìm, địa chỉ của bảng dò tìm phải là địa chỉ tuyệt đối.
3: Số thứ tự cột dữ liệu trên bảng dò tìm.
0: Kiểu tìm kiếm chính xác.

Sau khi điền xong công thức cho ô G4, tiếp tục kéo xuống copy công thức cho những nhân viên còn lại.

Hàm Vlookup nâng cao

Hình 2: Hàm Vlookup nâng cao

Xem thêm về hàm Vlookup: Tại đây

3.Sử dụng hàm Vlookup nâng cao với nhiều điều kiện.

Hàm vlookup nhiều điều kiện thực chất là hàm Vlookup thông thường, nhưng để sử dụng được thì ta gộp nhiều điều kiện thành 1 điều kiện bằng cách tạo cột phụ để hàm Vlookup hiểu và tìm kiếm.

Ví dụ: Bạn có một danh sách sản lượng sản xuất cho từng sản phẩm, từng ca. Làm thế nào để biết được sản lượng của 1 sản phẩm nào đó trong từng ca là bao nhiêu?

Hàm Vlookup nâng cao

Hình 3: Hàm Vlookup nâng cao

Với bài toán tìm kiến dữ liệu theo hàng ngang trong Excel chắc chắn bạn sẽ nghĩ ngay đến hàm Vlookup, nhưng ở đây ta cần tìm sản lượng của từng sản phẩm trong từng ca (2 điều kiện) mà hàm vlookup thông thường lại chỉ dùng được với 1 điều kiện.

Nên ta cần biến đổi điều kiện đầu vào từ 2 điều kiện thành 1 điều kiện bằng cách tạo ra một cột phụ mới từ việc ghép Sản phẩm và Ca.

Các bước thực hiện:

B1: Tạo cột phụ.

B2: Viết hàm với điều kiện tìm kiếm là cột phụ vừa tạo.

Chi tiết các bước:

B1: Tạo cột phụ:

Ta tạo thêm cột mới, cột này đứng ở trước cột Sản phẩm và được tạo ra bằng cách ghép cột Sản phẩm và cột Ca.

Công thức ghép: [Sản phẩm]&[Ca]

Với hàng đâu tiền thì công thức sẽ là: C4&D4

Sau khi tạo công thức cho hàng đầu ta copy công thức đó cho các hàng tiếp theo để hoàn thành cột. Sau khi hoàn thành thì cột phụ sẽ có dạng như hình sau:

Hàm Vlookup nâng cao

Hình 4: Hàm Vlookup nâng cao.

Bước 2: Viết công thức tìm kiếm với cột phụ vừa tạo.

Để tìm kiến sản lượng của 1 sản phẩm theo ca tại ô H6 ta nhập công thức: =VLOOKUP(H4&H5,$B$4:$E$8,4,0)

Trong đó:

H4&H5: Là giá trị cần đối chiếu.
$B$4:$E$8: Là bảng dò tìm bao gồm cả cột phụ
4: Số thứ tự cột dữ liệu trên bảng dò tìm.
0: Kiểu tìm kiếm chính xác.

Quan sát hình dưới để hiểu hơn về các thành phần của công thức chúng ta vừa tạo.

Hàm Vlookup nâng cao

Hình 5: Hàm Vlookup nâng cao.

Nếu muốn tìm sản lượng của sản phẩm khác hoặc ca khác bạn chỉ cần nhập sản phẩm và ca cần tìm vào bảng tìm kiếm như trên hình mà không cần tạo lại công thức.

Để hiểu hơn về hàm Vlookup nhiều điều kiện, bạn tham khảo: Hàm Vlookup 2 điều kiện, Hàm Vlookup nhiều điều kiện.

3.Sử dụng hàm Vlookup nâng cao để dò tìm từ phải qua trái.

Thông thường hàm Vlookup sẽ đối chiếu sang bên phải để lấy giá trị trả tương ứng và cột tìm kiếm phải dược đặt đầu tiên. Nhưng trong một số trường hợp chùng ta cần tìm kiếm, đối chiếu ngược lại từ phải qua trái để lấy giá trị tương ừng thì hàm Vlookup thông thường không thể dùng được.

Ví dụ: Ta có bảng mã vùng các tỉnh trên cả nước gồm cột mã vùng và cột Địa chỉ. Chúng ta cần tìm mã vùng của tỉnh thành cho trước?

Dò tìm từ phải qua trái trong Excel

Hình 6: Hàm Vlookup nâng cao.

Cột địa chỉ nằm ở phía bên phải của cột mã vùng, chiều tham chiếu là từ phải qua trái. Điều này ngược với cách tham chiếu thông thường của hàm vlookup, do đó chúng ta dùng hàm Lookup trong Excel để lấy mã vùng của tỉnh thành cho trước.

Cú pháp của hàm Lookup: =LOOKUP(Giá trị cần tìm,Vùng chứa giá trị cần tìm,Vùng chứa giá trị kết quả)

Trong đó:

Giá trị cần tìm: Có thể là số, văn bản, giá trị logic, tên hoặc tham chiếu tới 1 giá trị.
Vùng chứa giá trị cần tìm: Có thể là văn bản, số hoặc giá trị logic. Phạm vi chỉ có thể là 1 hàng hoặc 1 cột. Như đã nói ở trên, các giá trị trong vùng cần tìm phải sắp xếp tăng dần: 0, 1, 2… hoặc theo A, B, C…để hàm trả về giá trị chính xác. Văn bản không phân biệt chữ hoa hay chữ thường.
Vùng chứa giá trị kết quả: Phạm vi chỉ có thể là 1 hàng hoặc 1 cột và phải có cùng kích cỡ với vùng chứa giá trị cần tìm.

Với ví dụ trên, tại ô F6 ta nhập công thức: =LOOKUP(F4,C4:C10,B4:B10)

Trong đó:

F4: Là giá trị cần tìm.
C4:C10: Là vùng chứa giá trị cần tìm.
B4:B10: Là vùng chưa kết quả trả về.

Sau khi nhập xong công thức ta sẽ được kết quả như hình dưới.

Hàm Vlookup nâng cao

Hình 7: Hàm Vlookup nâng cao.

Xem thêm về hàm Vlookup ngược: Tại đây

Học Excel từ cơ bản đến nâng cao - Bài 55 Hàm Vlookup IF Left Right Mid Round Or va Cong Thuc Mang — Học Excel từ cơ bản đến nâng cao – Bài 55 Hàm Vlookup IF Left Right Mid Round Or va Cong Thuc Mang

Hướng dẫn chi tiết hàm Vlookup nâng cao trong Excel:

Sử dụng hàm Vlookup nâng cao với nhiều điều kiện

Hàm Vlookup nhiều điều kiện là hàm Vlookup cơ bản, nhưng để sử dụng được thì bạn phải tạo thêm một cột để hàm Vlookup tìm kiếm bằng cách tạo cột phụ, hãy xem ngay ví dụ dưới đây!

Bước 1: Tạo cột phụ cho bảng để tính sản lượng bằng cách:

Tạo thêm một cột mới bên cạnh cột sản phẩm và được tạo ra bằng cách ghép cột Sản phẩm và cột Ca làm việc với nhau.

= [Sản phẩm]&[Ca] =C3&D3

Sau khi tạo công thức cho hàng đầu, bạn copy công thức đó cho những hàng tiếp theo để hoàn thành cột phụ như hình:

Bước 2: Viết công thức tìm kiếm với dữ liệu ở cột phụ vừa tạo bằng công thức:

=VLOOKUP(H11&H12$B$3:$E$9;4;0)

Trong đó:

H11&H12: Là giá trị cần đối chiếu.
$B$3:$E$9: Là bảng dò tìm bao gồm cả cột phụ
4: Số thứ tự cột dữ liệu trên bảng dò tìm.
0: Kiểu tìm kiếm chính xác

Hàm vlookup nâng cao để dò tìm giá trị từ phải qua trái

Như các bạn đã biết, hàm Vlookup sẽ đối chiếu dữ liệu sang bên phải để nhận được giá trị trả về tương ứng. Tuy nhiên, nếu bạn cần tìm kiếm từ phải sang trái thì hàm Vlookup cơ bản không thể dùng được. Vậy nên bạn cần sử dụng hàm Vlookup nâng cao để giải quyết vấn đề này.

=LOOKUP (Giá trị cần tìm,Vùng chứa giá trị cần tìm,Vùng chứa giá trị kết quả)

Trong đó:

Giá trị cần tìm: Đây có thể là số, văn bản, giá trị logic, tên hoặc tham chiếu tới một giá trị mà bạn cần tìm
Vùng chứa giá trị cần tìm: Có thể là văn bản, số hoặc giá trị logic, chỉ có thể là một hàng hoặc một cột và sắp xếp tăng dần để hàm trả về giá trị chính xác.
Vùng chứa giá trị kết quả: Phạm vi chỉ có thể là một hàng hoặc một cột và có cùng kích cỡ với vùng chứa giá trị cần tìm.

Với ví dụ trên, tại ô H4 bạn nhập công thức: =LOOKUP(H3;E3:E9;C3:C9)

Trong đó:

H3: Là giá trị mà bạn cần tìm.
E3:E9: Là vùng chứa giá trị sản lượng cần tìm.
C3:C9: Là vùng chứa kết quả sản phẩm trả về.

Trên đây là bí quyết chinh phục hàm Vlookup nâng cao bằng cách tạo cột phụ, tìm kiếm nhiều điều kiện, dò tìm từ phải qua trái,…. Nếu có thắc mắc gì, bạn hãy bình luận phía bên dưới daotaotinhoc.vn sẽ hỗ trợ giải quyết vấn đề giúp bạn. Chúc các bạn thành cô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.

Hướng dẫn sử dụng Index và Match đúng cách (Hơn cả Vlookup)

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

Trong bài hướng dẫn về hàm VLOOKUP này, Blog Học Excel Online sẽ cố gắng dùng từ ngữ đơn giản để giải thích những điều cơ bản nhằm giúp quá trình học cho người mới bắt đầu trở nên dễ dàng nhất có thể. Chúng ta cũng sẽ khám phá vài công thức mẫu cho thấy cách dùng thông dụng nhất của hàm VLOOKUP trong Excel.

Xem nhanh

Vậy hàm VLOOKUP là gì? Để bắt đầu, nó là một hàm trong Excel. 🙂 Nó dùng để làm gì? Nó tìm kiếm giá trị mà bạn định rõ và trả về giá trị tương ứng ở một cột khác. Nói một cách kỹ thuật hơn, hàm VLOOKUP tìm kiếm giá trị từ cột đầu tiên trong trong dải ô xác định, rồi trả về giá trị tương ứng trong cùng một hàng từ một ô khác.

Ở cách dùng thông dụng nhất, hàm VLOOKUP trong Excel tìm kiếm thông qua danh sách dữ liệu bạn tạo ra dựa trên ký hiệu nhận dạng đặc biệt rồi trả về cho bạn một mẩu thông tin tương ứng với ký hiệu nhận dạng đặc biệt đó.

Ký tự “V” trong hàm VLOOKUP là viết tắt của từ “vertical” (“thẳng đứng”). Ký tự đó được dùng để phân biệt hàm VLOOKUP và hàm HLOOKUP – đây là hàm tìm kiếm giá trị từ hàng trên cùng của mảng (H là viết tắt của từ “horizontal” (“nằm ngang”)).

Hàm VLOOKUP có sẵn trong tất cả phiên bản, gồm Excel 2013, Excel 2010, Excel 2007, Excel 2007, Excel XP và Excel 2000.

Cú pháp hàm VLOOKUP trong Excel có dạng như sau:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Nếu bạn chưa nắm rõ các ý nghĩa công thức của hàm Vlookup và cách thức chúng hoạt động thì hãy đọc bài viết này trước Hàm Vlookup trong Excel và các hướng dẫn chi tiết qua các ví dụ dễ hiểu trước khi đọc phần nội dung phía đưới bạn nhé! Nếu bạn muốn đọc để hiểu bản chất và ứng dụng vào công việc chứ không phải “cưỡi ngựa xem hoa”

Xem thêm: Các hàm excel cơ bản

Tôi hy vọng rằng bạn đã quen dần với hàm VLOOKUP. Bây giờ, hãy khám phá vài ví dụ sử dụng công thức VLOOKUP cho dữ liệu thực.

Trong thực tiễn, công thức VLOOKUP rất hiếm khi được dùng để tìm kiếm dữ liệu trong cùng một bảng tính. Thông thường, bạn sẽ phải tìm kiếm và kéo dữ liệu tương ứng về từ bảng tính khác.

Để dùng hàm VLOOKUP từ bảng tính Excel khác, bạn nên nhập tên bảng tính và một dấu cảm thán trong câu lệnh table_array trước dải ô, ví dụ =VLOOKUP(40, Sheet2!A2:B15, 2). Công thức này chỉ ra rằng dải ô tìm kiếm A2:B15 nằm ở Sheet2.

Tất nhiên, bạn không cần phải nhập tên bảng tính một cách thủ công. Chỉ cần bắt đầu gõ công thức và khi gõ đến câu lệnh table_array, hãy đổi sang bảng tính cần tìm và dùng chuột để chọn dải ô.

Công thức bạn thấy trong ảnh chụp màn hình dưới đây tìm kiếm chuỗi ký tự “Product 1” (“Sản phẩm 1”) trong cột A (cột đầu tiên trong dải ô cần tìm A2:B9) ở bảng tính Giá tiền:

=VLOOKUP(“Product 1”, Prices!$A$2:$B$9, 2, FALSE)

Xin hãy chú ý rằng bạn phải đặt giá trị chuỗi ký tự cần tìm trong dấu ngoặc kép (“”) như cách bạn thường làm trong các công thức Excel.

Mẹo. Luôn luôn dùng tham chiếu ô tuyệt đối (có $) trong thông số table_array của công thức VLOOKUP là một ý tưởng tuyệt vời. Trong trường hợp này, dải ô cần tìm sẽ không thay đổi khi bạn sao chép công thức sang ô khác.

Để dùng hàm VLOOKUP giữa hai sổ làm việc Excel khác nhau, bạn nên đặt tên sổ làm việc trong dấu ngoặc vuông trước tên bảng tính.

Ví dụ, công thức dưới đây sẽ tìm kiếm giá trị “40” trong Sheet2 của sổ làm việc Numbers.xlsx:

=VLOOKUP(40, [Numbers.xlsx]Sheet2!A2:B15, 2)

Sau đây là cách dễ nhất để tạo công thức VLOOKUP trong Excel tham chiếu đến sổ làm việc khác:

Mở cả hai sổ làm việc. Bước này không bắt buộc, nhưng nó sẽ giúp việc lập công thức dễ dàng hơn bởi vì bạn sẽ không phải gõ tên sổ làm việc một cách thủ công. Bên cạnh đó, điều đó cũng sẽ bảo vệ công thức của bạn khỏi lỗi vô tình in sai.

Bắt đầu nhập công thức VLOOKUP, rồi đối với câu lệnh table_array, thì hãy đổi sang sổ làm việc khác rồi chọn dải ô cần tìm kiếm ở đó.

Ở công thức bạn thấy trong ảnh chụp màn hình dưới đây, sổ làm việc cần tìm là PriceList.xlsx và bảng tinh cần tìm là Prices.

Một khi bạn đóng sổ làm việc có bảng cần tìm thì công thức VLOOKUP của bạn sẽ vẫn chạy, nhưng nó sẽ hiển thị đầy đủ đường dẫn tới sổ làm việc cần tìm, như được diễn giải dưới đây:

Lưu ý. Nếu hoặc tên sổ làm việc hoặc tên bảng tính có khoảng trống, thì bạn nên đặt chúng trong dấu =VLOOKUP(40, ‘[Numbers.xlsx]Sheet2’!A2:B15,2)

Đừng bỏ lỡ: Tài liệu tự học Excel 2003

Giả sử, nếu phải sử dụng cùng một dải ô cần tìm trong vài công thức VLOOKUP, thì bạn có thể tạo tên cho một dải ô rồi nhập trực tiếp tên của dải ô vào công thức thay vì phải nhập vùng cần tìm (câu lệnh table_array).

Để tạo tên cho dải ô, bạn chỉ cần chọn dải ô đó và gõ bất kỳ tên nào vào Name box (Hộp tên) – ở bên trái thanh Công thức.

Và bây giờ bạn có thể viết công thức VLOOKUP dưới đây để tìm giá của Sản phẩm 1:

=VLOOKUP(“Product 1”, Products, 2)

Hầu hết tên dải ô trong Excel đều được sử dụng cho toàn bộ sổ làm việc, nên bạn không cần định rõ tên của bảng tính trong câu lệnh table_array, dù cho dải ô bạn cần tìm nằm ở bảng tính khác. Nếu nó nằm ở sổ làm việc khác, thì bạn phải đặt tên sổ làm việc phía trước tên dải ô, ví dụ:

=VLOOKUP(“Product 1”, PriceList.xlsx!Products,2)

Những công thức như thế này này dễ hiểu hơn rất nhiều, đúng không? Bên cạnh đó, việc sử dụng tên cho dải ô có thể là một sự thay thế tuyệt vời cho tham chiếu ô tuyệt đối. Bởi vì tên dải ô không đổi khi sao chép công thức sang các ô khác, nên bạn có thể chắc rằng bạn sẽ luôn tìm đúng dải ô.

Nếu bạn đã đổi dải ô thành bảng Excel có đầy đủ tính năng (Insert tab > Table), thì bạn có thể dùng chuột chọn dải ô, và Microsoft Excel sẽ tự động thêm tên các cột (hay tên bảng trong trường hợp bạn đã chọn toàn bộ bảng) vào công thức:

Công thức đầy đủ có thể trông như thế này:

=VLOOKUP(“Product 1”, Table46[[Product]:[Price]],2)

hay thậm chí là có dạng như thế này: =VLOOKUP(“Product 1”, Table46,2)

Cũng như các công thức khác, bạn có thể sử dụng ký tự đại diện cho hàm VLOOKUP trong Excel:

Dấu chấm hỏi (?) để khớp với bất cứ ký tự riêng lẻ nào, và

Dấu sao (*) để khớp với bất kỳ dãy ký tự nào.

Việc sử dụng ký tự đại diện trong công thức VLOOKUP có thể rất hữu ích trong nhiều trường hợp:

Khi bạn không nhớ chính xác chuỗi ký tự bạn đang tìm kiếm.

Khi bạn muốn tìm một từ nào đó. Từ đó là một phần của nội dung ô. Hãy chú ý rằng hàm VLOOKUP tìm kiếm toàn bộ nội dung ô, như thể bạn đã chọn “Match the entire content” (“Khớp toàn bộ nội dung ô”) trong tương tác Tìm kiếm chuẩn của Excel.

Khi cột cần tìm có khoảng trống thụt đầu dòng hay cuối dòng. Nếu gặp trường hợp này, thì bạn có thể vắt óc cố tìm ra lý do tại sao công thức thông thường của bạn lại không được thực hiện.

Giả sử, bạn muốn tìm một khách hàng nhất định trong cơ sở dữ liệu dưới đây. Bạn không thể nhớ tên của anh ấy, nhưng bạn biết rằng nó bắt đầu bằng “ack”. Vậy thì, hàm VLOOKUP dưới đây sẽ giúp bạn rất nhiều đấy:

=VLOOKUP(“ack*”,$A$2:$C$11,1,FALSE)

Một khi bạn chắc mình đã tìm đúng tên rồi, bạn có thể sử dụng công thức VLOOKUP tương tự để tìm số tiền người khách hàng ấy đã trả. Bạn chỉ cần thay đổi thông số thứ ba trong công thức thành số thứ cột mà bạn cần tìm, đó là cột C (3) trong trường hợp này:

=VLOOKUP(“ack*”,$A$2:$C$11,3,FALSE)

Sau đây là thêm vài ví dụ về việc sử dụng hàm VLOOKUP có ký tự đại diện:

=VLOOKUP(“*man”,$A$2:$C$11,1,FALSE) – tìm tên kết thúc bằng “man”.

=VLOOKUP(“ad*son”,$A$2:$C$11,1,FALSE) – tìm tên bắt đầu bằng “ad” và kết thúc bằng “son”.

=VLOOKUP(“?????”,$A$2:$C$11,1,FALSE) – tìm họ có năm ký tự.

Lưu ý. Để công thức VLOOKUP có ký tự đại diện chạy chính xác, bạn phải luôn thêm FALSE như một thông số cuối cùng. Nếu dải ô cần tìm của bạn có hơn một mục nhập khớp với tiêu chuẩn đại diện, thì giá trị tìm thấy đầu tiên sẽ được trả về.

Bây giờ, hãy thảo luận thêm về một ví dụ phức tạp hơn – đó là làm thế nào để tìm kiếm giá trị ở một ô nào đó. Giả sử, bạn có mã bản quyền ỏ cột A và tên bản quyền ở cột B. Bạn cũng có một phần (vài ký tự) của một mã bản quyền nào đó ở cột C1 và bạn muốn tìm Tên Bản quyền tương ứng.

Điều này có thể được giải quyết bằng cách dùng hàm VLOOKUP có dạng như sau:

=VLOOKUP(“*”&C1&”*”, $A$2:$B$12, 2, FALSE)

Công thức này tìm kiếm giá trị ở ô C1 thông qua một dải ô xác định và trả về giá trị tương ứng ở cột B. Xin hãy chú ý rằng chúng ta sử dụng dấu và (&) trước và sau một tham chiếu ô trong thông số đầu tiên nhằm nối chuỗi ký tự.

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, hàm VLOOKUP của tôi trả về “Jeremy Hill” bởi vì mã bản quyền của anh ấy có một nhóm ký tự nằm trong ô C1:

Đồng thời, xin hãy chú ý câu lệnh table_array trong ảnh chụp màn hình trên. Nó có tên bảng (“Bảng 7”) thay vì dải ô, điều này chúng ta đã bàn ở ví dụ trên.

Và cuối cùng, hãy xem xét kỹ câu lệnh cuối cùng mà bạn nhập vào hàm VLOOKUP trong Excel – range_lookup. Vì đã được nói đến ở đầu bài hướng dẫn, câu lệnh này cực kỳ quan trọng bởi vì bạn có thể nhận được kết quả khác nhau tùy vào bạn nhập TRUE hay FALSE.

Đầu tiên, hãy xem “giá trị tìm kiếm chính xác” và “giá trị tìm kiếm tương đối” trong Microsoft Excel thật sự có nghĩa gì.

Nếu range_lookup được cài đặt là FALSE, thì công thức sẽ tìm chính xác giá trị, ví dụ, công thức sẽ tìm chính xác giá trị cần tìm khi bạn nhập giá trị đó làm thông số thứ nhất (lookup_value).

Nếu có hai hay nhiều hơn hai giá trị từ cột đầu tiên của table_array khớp với giá trị cần tìm, thì kết quả tìm thấy đầu tiên sẽ được trả về. Nếu không thể tìm thấy giá trị tìm kiếm chính xác, thì lỗi #N/A sẽ được trả về.

Ví dụ, nếu bạn sử dụng công thức =VLOOKUP(4, A2:B15,2,FALSE), nhưng dữ liệu của bạn không chứa giá trị 4 từ ô A2 đến cột A15, thì công thức sẽ trả về lỗi #N/A.

Nếu range_lookup được cài đặt thành TRUE hay được loại bỏ, thì công thức sẽ tìm sự phù hợp tương đối. Nói chính xác hơn, công thức VLOOKUP của bạn sẽ tìm kiếm sự phù hợp tuyệt đối đầu tiên và nếu sự phù hợp tuyệt đối không được tìm thấy, thì nó sẽ trả về sự phù hợp tương đối. Gía trị tìm kiếm tương đối là giá trị lớn nhất tiếp theo, chỉ nhỏ hơn lookup_value.

Lưu ý quan trọng! Nếu bạn nhập TRUE hay loại bỏ câu lệnh range_lookup, thì các giá trị ở cột đầu tiên của dải ô cần tìm phải được sắp xếp theo thứ tự tăng dần, đó là từ nhỏ nhất đến lớn nhất. Nếu không thì, hàm VLOOKUP trong Excel có thể sẽ không tìm được giá trị chính xác.

Để biết thêm tầm quan trọng của việc định rõ TRUE và FALSE, hãy thử thêm vài công thức VLOOKUP và xem kết quả chúng trả về nhé.

Có thể bạn còn nhớ, để tìm kiếm sự phù hợp tuyệt đối, bạn phải đặt FALSE làm câu lệnh cuối cùng trong công thức VLOOKUP trong Excel.

Hãy chọn bảng “Animal speed” để làm ví dụ đầu tiên và tìm ra loài vật nào có thể chạy 80,5 km một giờ. Tôi tin chắc bạn sẽ chẳng gặp khó khăn gì với công thức này:

=VLOOKUP(50, $A$2:$B$15, 2, FALSE).

Hãy lưu ý rằng, dải ô cần tìm của chúng ta (cột A) chứa hai giá trị 50 lần lượt trong cột A5 và A6; và công thức trả về giá trị trong cột A5. Tại sao lại như thế? Bởi vì hàm VLOOKUP có giá trị tìm kiếm tuyệt đối sẽ trả về giá trị tìm thấy đầu tiên – giá trị khớp với giá trị cần tìm.

Lớp học Excel tại Hà Nội

Khi sử dụng công thức VLOOKUP có giá trị tìm kiếm tương đối, đó là có range_lookup được cài đặt thành TRUE hay được bỏ đi, thì điều đầu tiên bạn cần làm đó là lọc cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.

Điều này rất quan trọng bởi vì công thức VLOOKUP sẽ trả về giá trị lớn nhất tiếp theo cho giá trị cần tìm mà bạn đã định rõ rồi sẽ ngừng tìm kiếm. Nếu bạn không lọc dữ liệu một cách chính xác, thì cuối cùng bạn sẽ nhận được một kết quả lạ hay lỗi #N/A.

Và bây giờ, bạn có thể sử dụng một trong hai công thức này:

=VLOOKUP(69, $A$2:$B$15, 2, TRUE)

=VLOOKUP(69,$A$2:$B$15,2)

Như bạn có thể thấy, tôi đang cố tìm loài vật có tốc độ chạy gần 111 km một giờ nhất. Và đây là kết quả mà công thức VLOOKUP trả về:

Như bạn có thể thấy, công thức trả về “Antelope” (“Linh dương”) – nó có tốc độ chạy 98 km trên giờ, trong khi đó, chúng ta cũng có báo chạy với tốc độ 113 km trên giờ, và 70 thì gần với 69 hơn 61 , đúng không nào? Vậy, tại sao lại như thế? Bởi vì hàm VLOOKUP có giá trị tìm kiếm tương đối sẽ trả về giá trị lớn nhất tiếp theo, và giá trị này nhỏ hơn giá trị cần tìm.

Hy vọng rằng, những ví dụ này đã giúp việc sử dụng Excel dễ dàng hơn và hàm này không còn xa lạ với bạn nữa 🙂 Bây giờ, sẽ rất tuyệt để tổng kết những điều quan trọng mà bạn đã học nhằm giúp bạn nhớ các điểm mấu chốt tốt hơn.

Hàm VLOOKUP trong Excel không thể tìm ở bên trái. Nó luôn luôn tìm giá trị nằm trong cột cận trái của dải ô cần tìm (table_array).

Trong công thức VLOOKUP, tất cả giá trị đều không phân biệt dạng chữ, có nghĩa là ký tự viết hoa và viết thường đều được xử lý như nhau.

Nếu giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của dải ô cần tìm, thì hàm VLOOKUP sẽ trả về lỗi #N/A.

Nếu câu lệnh thứ ba (col_index_num) nhỏ hơn 1, thì công thức VLOOKUP sẽ trả về lỗi #VALUE!. Trong trường hợp, nó lớn hơn số cột trong dải ô cần tìm (table_array), thì công thức sẽ trả về lỗi #REF!.

Hãy sử dụng tham chiếu ô tuyệt đối trong câu lệnh table_array của công thức VLOOKUP để có đúng dải ô cần tìm khi xử lý công thức. Hãy cân nhắc việc sử dụng tên cho dải ô hay cho bảng trong Excel như một phương án thay thế.

Khi tìm kiếm sự phù hợp tương đối (range_lookup được cải đặt thành TRUE hay loại bỏ), hãy luôn lọc dữ liệu ở cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.

Và cuối cùng, hãy nhớ tầm quan trọng của thông số cuối cùng. Sử dụng TRUE hay FALSE khi thích hợp và bạn sẽ không phải đau đầu nữa.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học

Xem thêm: Cách sử dụng hàm vlookup nhiều điều kiện – có ví dụ cụ thể minh họa

Hàm Vlookup là một trong những hàm mà được nhiều người sử dụng phổ biến hiện nay. Việc kết hợp các hàm khác với Vlookup cơ bản thì lại không thể giải quyết được các vấn đề nâng cao. Qua đó, chúng ta cần sử dụng hàm Vlookup nâng cao để giải quyết vấn đề trên, hãy cùng daotaotinhoc.vn tìm hiểu bài viết dưới đây nhé.

Xem thêm:Bí kíp giúp bạn dùng hàm IFERROR VLOOKUP trong Excel dễ dàng – Có ví dụ minh hoạCách sử dụng hàm vlookup 2 điều kiện trong Excel cực kỳ đơn giản – Có ví dụ minh hoạ chi tiết

LƯU Ý

Cột đầu tiên của vùng tìm kiếm phải chứa điều kiện tìm kiếm.

Khi chèn cột ở giữa cột đầu tiên và cột chứa giá trị tìm kiếm cần phải sửa lại số thứ tự cột.

Vlookup có thể tìm kiếm theo điều kiện chính xác hoặc tương đối.

ĐÀO TẠO EXCEL DOANH NGHIỆP

Chương trình đào tạo Excel nghiệp được thiết kế chuyên biệt và phù hợp với từng doanh nghiệp.

Không đi theo 1 giáo án chung. Điều này giúp cho chất lượng đào tạo Excel ở mức cao nhất.

TỔNG HỢP MẪU EXCEL (PRO)

Webkynang Việt Nam là doanh nghiệp dẫn đầu tại Việt Nam về phát triển các ứng dụng, file mẫu Excel dành cho doanh nghiệp.

Các mẫu file đáp ứng gần như đầy đủ các nhu của doanh nghiệp. Đặc biệt là dịch vụ thiết kế riêng theo yêu cầu

Trường

Giảng viên

Chuyên gia về Đào tạo Excel & Thiết kế File Excel quản lý.

“Kiến thức là để cho đi”

38 Comments

Kimtuyen on August 12, 2016 at 6:53 am

Cảm ơn bài viết của ad rất nhiều.

Đúng như ad nói, bài viết có chất lượng nhất hiện nay rồi.

Hi vọng bạn sẽ thường xuyên update thêm kiến thức mới về hàm vlookup để mình và các bạn khác được cải thiện khả năng excel của mình.
- HOANG ANH on August 24, 2016 at 5:37 pm
  
  KHÔNG CÓ LỜI GIẢI 🙁 TỚI BÀI TẬP NÂNG CAO TIM ĐƠN GIÁ XUẤT NHẬP LÀ E THẤY LOÀI CÁI NGU CỦA E RỒI CÓ AI GIÚP EM VỚI
  - Hưng Võ on August 30, 2016 at 3:05 pm
    
    Theo gợi ý của ad mình làm như vầy ra hợp lý này bạn, không biết có nào gọn hơn không: =IF(LEN(A4)=4,VLOOKUP(LEFT(A4,3),IF(RIGHT(A4,1)=”X”,$A$13:$B$16,$D$13:$E$17),2,0),VLOOKUP(LEFT(A4,4),IF(RIGHT(A4,1)=”X”,$A$13:$B$16,$D$13:$E$17),2,0))
    - hung on September 10, 2016 at 4:21 am
      
      =VLOOKUP(IF(LEN(A4)=4,LEFT(A4,3),LEFT(A4,4)),IF(RIGHT(A4,1)=”X”,$A$13:$B$16,$D$13:$E$17),2,0)
      - hung on September 14, 2016 at 12:40 am
        
        Cảm ơn anh nhiệt tình hướng dẫn, em mới tìm hiểu học cái này mà,em cũng học thương mại ra, đọc qua giới thiệu về anh rồi, cũng thấy vui!
        
        =VLOOKUP(LEFT(A4,LEN(A4)-1),IF(RIGHT(A4,1)=”X”,$A%13:$B$16,$D$13:$E$17),2,0)
      - Thanh Phong on October 30, 2016 at 9:18 pm
        
        Giải bài nâng cao 2 dùm e luôn đi a, khó quá
      - QUANG on December 18, 2016 at 1:50 am
        
        =IF(RIGHT(A4,1)=”X”,VLOOKUP(LEFT(A4,LEN(A4)-1),$A$14:$B$16,2,0),VLOOKUP(LEFT(A4,LEN(A4)-1),$D$14:$E$17,2,0)
      - THANGTHOM on September 29, 2017 at 11:00 pm
        
        Mình có cách này mọi người góp ý kiến giúp mình nhé:
        
        =vlookup(isna(vlookup(left($c3,3)&”*”,$b$16:$D$24,2,0),VLOOKUP(LEFT($C3,2),$B$16:$D$24,2,0),VLOOKUP(LEFT($C3,3)&”*”,$B$16:$D$24,2,0))
Nguyễn Trung on August 25, 2016 at 10:39 pm

Thanks ad, bài viết rất dễ hiểu !
ĐỒng on September 1, 2016 at 9:09 am

cho em hỏi. em có 2 cột là số thửa mới và tờ bản đồ mới, cần dò xem có trùng với 2 cột đó không ở bảng khác để lấy dữ liệu số thửa cũ và tờ bản đồ cũ điền vào 2 cột số thửa cũ và tờ bản đồ cũ của bảng này phải làm như thế nào ạ
QUANG on December 18, 2016 at 7:34 pm

ad cho em công thức giải bài 2 nâng cao luôn với đc k ạ? Khó quá 🙁 Cảm ơn ad nhiều 😀
phạm thành luận on December 25, 2016 at 10:56 am

Có bạn nào bik cách dùng hàm tìm kiếm vlookup tìm kiếm ngược lại không?

Ví dụ ta có 2 cột : Mã KH và Tên KHỞ cột Mã KH: NV01 – Cột Tên KH – Nguyễn Văn AThông thường mình sẽ tìm theo mã “NV01” là ra tên “Nguyễn Văn A”.Nhưng mình muốn tìm ngược lại “Nguyễn Văn A” ra mã “NV01”.Có bạn nào đã làm được rồi chỉ mình với.Tk ALL!
gà đồi on January 5, 2017 at 1:01 am

thank ad nhé.học lâu k dùng đến h quên hết may mà có bài này mk tìm lại được chút kiến thức.bài viết rất dễ hiểu….:))
ngoan on January 20, 2017 at 10:49 am

giúp mình vói các bác oi

mình dùng ham vlookup de tim ten khach hàng cua ma can ho dien tich can ho cua so hop dong thi lam sao ban
ngoan on January 20, 2017 at 10:53 am

Giup minh voi

minh can tim ten khach hang trong bang do co ma can ho dien tich va so hop donggiup minh voi
Hà Thuỷ on February 26, 2017 at 3:37 pm

Bài viết rất hữu ích; mình còn vướng vài chỗ khi kết hợp vlookup với hàm khác. Đọc xong bài viết; bây giờ có thể làm chủ hàm vlookup. Thanks!
Phượng on May 27, 2017 at 11:21 pm

Giúp mình giải bài này với
Nguyen Huy on June 3, 2017 at 1:41 pm

Có cách nào tính tổng các giá trị khi mình dùng Vlookup dò tìm ko Ad?

Ví dụ : 1 chi tiết ký hiệu mã AA nằm trong bó 13 Chi tiết AA nằm trong bó 3Mình dùng Vlookup dò Tìm AA rồi nhưng không cộng tổng = 3 chi tiết AA sau khi dò tìm ra được.Giúp mình với.!
Hai on August 4, 2017 at 10:36 am

Mình đang làm bài dò tìm mà trị dò có nhiều mã không có trong bảng dò. Những mã đó được ghi trong bảng dò là chữ Khác. Vậy phải làm thế nào ạ?

Ví dụ: trị dò gồm A, B, E, F, G,H, Y,ZTrị so sánh trong bảng dò: A, G,Y,Z và KHÁCCảm ơn Ad!
thuphuong on August 4, 2017 at 11:53 pm

có bạn nào bít dùng ham tìm kiem để tìm kiểm cho trường hợp nay không ??

vd: bảng 1 :code kh 00001 / tên kh Abảng 2 : cũng la tên kh A nhưng code thay dổimình mún dựa vào tên kh A để lấy code mới thì lam sao ạ..Giúp mình với ??
Nguyễn Mạnh Hoàn on September 5, 2017 at 8:48 am

Chào bạn mình có bài toán này nhờ bạn giúp.

Một lớp có khoảng 50 bạn học sinh. Từ 50 bạn đó trường thống kê một số thông số như: tên, chiều cao, cân nặng, nhóm máu, giới tính. Như vậy về cơ bản đã có một bảng excel các thông tin.Từ các thông tin ấy (tên, chiều cao, cân nặng….) thì nhà trường dựa vào bảng tính excel sẽ suy ra một số thông số khác ví dụ như: A, B, C, D.Tương ứng với mỗi học sinh là một số thứ tự 1,2,3….Bây giờ nhà trường mong muốn tạo một bảng mà tính. Mà ví dụ chỉ cần nhập số 1 là sẽ ra các thông số A, B, C, D ấy luôn(Các thông số đã thống kê không cần hiện).Nhờ bạn giúp đỡ mình.Cảm ơn bạn.
Nhung on December 13, 2017 at 7:02 pm

Chào bạn! Mình muốn lấy tên sp từ sheet này sang sheet kia thông qua Mã sp, mình dùng hàm Vlookup, công thức dòng đầu tiên đúng, đánh mã vào thì tên tự động hiện ra, copy xuống thì từ dòng thứ 2 trở đi đều hiện #N/A, nhưng khi mình nhập mã vào hàng thứ hai thì tên sp vẫn tự động hiện ra, có nghĩa là hàm vẫn đúng, nhưng vẫn hiện #N/A khi chưa đụng tới nó. Lỗi này khắc phục bằng cách nào vậy?
Duyen My on March 14, 2018 at 3:04 pm

Cảm ơn những chia sẻ của Ad, rất hữu ích cho mọi công việc, mong sẽ nhận được những bài viết như vậy để cập nhật kịp kiến thức excel vào trong công việc

Cho đi rồi sẽ nhận lại, chúc ad may mắn và gặt hái nhiều thành công!!!
Nguyen Truong on July 18, 2018 at 5:07 pm

Xin chào,

Tôi cần sự giúp đỡ của Ad để lập công thức cho excel với mục đích như sau:Tôi muốn theo dõi doanh thu nên tạo lập thành 2 sheet: một sheet thể hiện doanh thu về tài khoản, còn 1 sheet thể hiện thông tin tổng hợp.Tôi muốn tại bảng Thông tin Tổng hợp có cột thể hiện ngày Doanh thu được cập nhật vào gần nhất (mỗi cột thể hiện 1 tháng). Vậy tôi có thể sử dụng công thức như nào tại Sheet Thông Tin Tổng Hợp ?Mong nhận được sự hỗ trợ của AD.Trân trọngNguyen Truong
Trần Thị Thịnh on February 22, 2019 at 12:58 pm

Đây là bài viết đầy đủ, sáng tạo và dễ hiểu nhất tính tới thời điểm này về cách sử dụng của cái hàm dễ mà khó này. Thanks so much!

Trackbacks/Pingbacks

Cách Sử dụng Hàm Vlookup Trong Excel l Bài tập hàm vlookup l Cách dùng – […] Mời bạn xem bài: Sử dụng hàm Vlookup để tìm kiếm dữ liệu từ sheet, file khá… […]
Hàm Vlookup – Hàm Hlookup kết hợp hàm IF bài tập vlookup – […] Mời bạn xem bài: Sử dụng hàm Vlookup để tìm kiếm dữ liệu từ sheet, file khá… […]
Hàm vlookup nâng cao – bài tập hàm vlookup nâng cao – […] 4. Cách sử dụng hàm vlookup để tìm kiếm dữ liệu từ sheet khác hoặc file khác […]
Kiến thức quản lý | KỸ NĂNG Quản Lý – […] Sử dụng hàm vlookup để tìm kiếm từ SHEET & file khác […]
Cách sử Hàm Match trong excel từ Cơ bản – Nâng cao (PRO) – […] bạn muốn tìm hiểu cặn kẽ về hàm vlookup thì bạn nên đọc bài viết “Tất tần tật về…
Bài tập excel số 9: cấp độ đơn giản – có đáp án – Học Excel – […] sử dụng hàm vlookup các bạn tham khảo bài viết về hàm tìm kiếm vlookup […]
Lỗi #N/A là gì, nghĩa là gì,… lỗi #N/A trong hàm vlookup – […] khi đọc bài viết của Thầy Trường tôi mới biết Hàm Vlookup lại có nhiều cách kết hợp tới…
Hàm INDEX Cú pháp, Ví dụ, Bài tập (Index kết hợp Match = Vlookup) – […] => Xem thêm: Tổng hợp Cách sử dụng hàm Vlookup trong excel […]
VLOOKUP 2 điều kiện trong excel – Dễ làm & Hiệu quả cao – Học Excel Online Miễn phí – […] => Xem thêm: Tổng hợp về hàm vlookup: Cơ bản – Nâng cao […]
Công thức trong excel không chạy: Tại sao – Cách xử lý NGAY – […] hình trên là hướng dẫn cách xử lý hàm vlookup không chạy công thức […]
Cách tạo LISTBOX trong excel – Drop list động rất thông minh (2007 2010 2013) – Học Excel Online Miễn phí – […] Khi đó ta sẽ kết hợp data validation để tạo list mã hàng, sau đó dùng hàm vlookup để…
Gộp hai cột họ và tên trong excel như thế nào? – Học excel miễn phí Webkynang – […] Xem thêm: Tất tần tật về hàm Vlookup – Từ cơ bản đến nâng cao PRO […]
Cách chuyển đổi cột thành hàng, hàng thành cột trong excel – […] Bài viết hay đọc: Hàm Vlookup cơ bản, nâng cao […]
Chèn ảnh cố định vào ô trong excel – Học Excel miễn phí – […] Hàm excel Vlookup cơ bản, nâng cao […]
Hàm vlookup nâng cao – download bài tập hàm vlookup – […] Tất tần tật về hàm vlookup -> Xem ngay […]
Kết hợp hàm Vlookup hàm Left l hàm vlookup kết hợp left – […] – Cú pháp hàm vlookup: Bạn xem trong bài viết […]
Cố định vùng dữ liệu trong Excel – Ý nghĩa của $ – Học Excel FREE Webkynang – […] Hàm Vlookup: Áp dụng trong trường hợp tự động tên hàng, DVT hay tên KH, Địa chỉ khi đã…
Hàm FILTER trong excel – Lấy sổ chi tiết, phiếu nhập, xuất – Học Excel FREE Webkynang – […] thường ta sẽ nghĩ tới hàm vlookup bởi đó là hàm dò tìm đúng không […]
Giới thiệu hàm XLOOKUP – công cụ mạnh mẽ đang thử nghiệm – Webkynang – […] là hàm tìm kiếm theo cột. Trong khi Hlookup là hàm tìm kiếm theo […]
Hướng dẫn sử dụng 13 hàm Excel cơ bản nhất và thường được sử dụng | Thủ thuật máy in – […] Xem thêm: Cách sử dụng chức năng vlookup từ cơ bản đến nâng cao […]
Hàm VALUE trong excel dùng để làm gì? và cách viết hàm ra sao – Học Excel FREE Webkynang – […] 1 số được lưu dưới dạng văn bản dùng để tính toán hay tìm kiếm dữ liệu có thể…
+200 hàm Excel | Từ điển online | Tra cứu dễ dàng | Hướng dẫn + Ví dụ – Học Excel FREE Webkynang – […] VLOOKUP […]
Ham – Học Excel FREE Webkynang – […] Cách dùng hàm Vlookup cơ bản […]
8+ Lưu ý quan trọng khi sử dụng hàm VLOOKUP trong excel – […] ý quan trọng khi sử dụng hàm VLOOKUP trong excel. Hàm VLOOKUP là hàm cực kỳ mạnh trong công…
Dùng hàm Vlookup để Tìm kiếm dữ liệu từ phải sang trái – Webkynang – Học Excel FREE – […] Kiến thức chung về hàm Vlookup: Định nghĩa, cú pháp: Click xem […]
Ham Lookup Trong Excel – […] Download Image More @ excel.webkynang.vn […]

Submit a Comment

NHẠC TRỮ TÌNH BOLERO ĐỈNH CAO CHẤT LƯỢNG NGHE LÀ PHÊ NGAY TỪ BÀI ĐẦU ĐẾN CUỐI - CẢNH ĐẸP CHÂU ÂU 4K — NHẠC TRỮ TÌNH BOLERO ĐỈNH CAO CHẤT LƯỢNG NGHE LÀ PHÊ NGAY TỪ BÀI ĐẦU ĐẾN CUỐI – CẢNH ĐẸP CHÂU ÂU 4K

Hướng dẫn được thực hiện trên Laptop hệ điều hành Windows 10 với phiên bản Excel 2013. Bạn cũng có thể thực hiện tương tự trên các sản phẩm Excel 2007, 2010, 2013, 2016, 2109 và trên các dòng laptop có cùng hệ điều hành hay dòng MacOS có cùng thao tác khác.

Cú pháp hàm VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Trong đó:

Lookup_value: Là giá trị dùng để dò tìm.
Table_array: Là vùng dữ liệu chứa giá trị cần dò tìm. Vùng có từ 2 cột trở lên.
Col_index_num: Là số thứ tự cột đếm từ vùng dò tìm chứa giá trị cần trả về.
Range_lookup: Là phương pháp dò tìm. Range_lookup là 0 hoặc FALSE để tìm giá trị chính xác, range_lookup là 1 hoặc TRUE hoặc bỏ qua để tìm giá trị gần đúng.

Ví dụ: Dùng hàm VLOOKUP để tìm giá trị trong bảng dữ liệu sau.

Bước 1: Bạn nhập hàm =VLOOKUP(D9,A1:E6,2,1) vào ô tham chiếu muốn hiển thị kết quả.

Ví dụ minh họa hàm VLOOKUP

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Nhấn Enter để hiển thị kết quả

Mua phần mềm Microsoft Office tại Thế Giới Di Động:

Bài tập hàm VLOOKUP nâng cao trong Excel có lời giải

Bài tập 1

Sử dụng hàm VLOOKUP để điền tên hàng theo mã hàng trong bảng thông tin sau.

Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =VLOOKUP(LEFT(A4,2),$A$13:$B$16,2,0) vào ô tham chiếu tên hàng.

Giải thích hàm:

Hàm LEFT ở vị trí lookup_value để cắt chuỗi ký tự sao cho trùng với ký tự ở bảng thông tin.
Vùng dữ liệu table_array chỉ cần sử dụng 2 cột vì cột thử 2 có chứa giá trị cần dò tìm.

Chú ý:

Sau khi quét chọn vùng dữ liệu table_array ta cần phải nhấn phím F4 để cố định vùng dữ liệu đó.

Ví dụ hàm VLOOKUP kết hợp hàm LEFT để dò tìm dữ liệu

Bước 2: Nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị các kết quả còn lại như hình.

Nhấn phím Enter để hiển thị kết quả

Sử dụng hàm VLOOKUP để tính doanh thu trong bảng dữ liệu sau.

Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =E4*VLOOKUP(LEFT(A4,2),$A$13:$E$16,IF(RIGHT(A4,1)=”1″,3,IF(RIGHT(A4,1)=”2″,4,5)),0) vào ô tham chiếu doanh thu.

Ví dụ hàm VLOOKUP kết hợp nhiều hàm

Bước 2: Nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị các kết quả còn lại như hình.

Nhấn phím Enter để hiển thị kết quả

Sử dụng hàm VLOOKUP phương pháp tìm gần đúng để điền đơn giá vào bảng sau.

Bài tập

Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =VLOOKUP(C4,$A$14:$B$16,2,1) vào ô tham chiếu đơn giá.

Ví dụ hàm VLOOKUP phương pháp dò tìm gần đúng

Bước 2: Nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị các kết quả còn lại như hình.

Nhấn phím Enter để hiển thị kết quả

Sử dụng hàm VLOOKUP để tính doanh thu trong bảng sau.

Bài tập

Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =VLOOKUP(C4,$A$14:$B$16,2,1)*HLOOKUP(F4,$E$12:$H$13,2,1) vào ô tham chiếu doanh thu.

Ví dụ minh họa hàm VLOOKUP kết hợp hàm HLOOKUP

Bước 2: Nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị các kết quả còn lại như hình.

Nhấn phím Enter để hiển thị kết quả

Trong bài viết này chúng ta sẽ cùng tìm hiểu về các công thức Vlookup nâng cao, cách sử dụng hàm VLookup lồng các hàm khác để tìm kiếm theo nhiều điều kiện, cách tra cứu hai chiều với hàm VLOOKUP. Hãy cùng tìm hiểu nào:

Xem nhanh

Hàm VLOOKUP thực sự hữu ích khi tìm kiếm một giá trị nhất định trên một cơ sở dữ liệu. Tuy nhiên, nó có điểm hạn chế lớn – cú pháp của nó chỉ cho phép tra cứu một giá trị. Nếu bạn muốn tìm kiếm theo nhiều điều kiện thì sao? Ta có giải pháp dưới đây.

Giả sử bạn có một danh sách các đơn đặt hàng và bạn muốn tìm Qty (Số lượng) dựa trên 2 tiêu chí – “Name” và “Product”. Phức tạp ở chỗ là mỗi khách hàng đặt hàng nhiều sản phẩm, như bạn thấy trong bảng dưới đây:

Một công thức VLOOKUP thông thường sẽ không giải quyết được trong trường hợp này, bởi nó trả về giá trị tìm thấy đầu tiên phù hợp với giá trị tra cứu mà bạn chỉ định. Vì vậy, nếu bạn muốn biết số lượng “Sweets” được sắp xếp bởi “Jeremy Hill” và viết công thức =VLOOKUP(B1,$A$5:$C$14,3,FALSE), kết quả đầu tiên tìm thấy trả về là “15” tương ứng với “Apples”.

Giải pháp đơn giản là tạo một cột bổ sung nối tất cả các tiêu chí mà bạn muốn, trong ví dụ này là các cột Customer và Product . Hãy nhớ rằng cột liên kết luôn luôn là cột ngoài cùng bên trái trong phạm vi tra cứu của bạn vì đây là nơi mà VLOOKUP luôn tìm kiếm giá trị tra cứu.

Vì vậy, bạn thêm một cột phụ vào bảng của bạn và sao chép một công thức như thế này =B2&C2 qua cột đó (hoặc =B2&” “&C2 nếu bạn muốn tách các giá trị được nối bằng khoảng trắng để làm cho dữ liệu dễ đọc hơn).

Và sau đó, bạn có thể sử dụng một công thức VLOOKUP đơn giản như sau:

=VLOOKUP(“Jeremy Hill Sweets”,$A$5:$C$14,3,FALSE)

hoặc là

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

Trong đó B1 chứa giá trị tra cứu (lookup_value) và 3 là số cột chứa dữ liệu bạn muốn tìm (col_index_num).

Ví dụ 2. Vlookup với 2 tiêu chí từ một bảng tính khác

Nếu bạn cần phải cập nhật bảng chính với dữ liệu từ bảng khác (bảng hoặc trang tính khác), thì bạn có thể nối các giá trị tra cứu trực tiếp vào công thức mà bạn nhập vào bảng chính của bạn.

Giống ví dụ trên, bạn cần thêm một cột phụ vào bảng tra cứu với các giá trị tra cứu kết nối và nên tạo ở vị trí ngoài cùng bên trái của dãy tìm kiếm.

Vì vậy, công thức VLOOKUP của bạn có thể trông như thế này:

=VLOOKUP(B2&” “&C2,Orders!$A&$2:$B$2,4,FALSE).

Trong đó các cột B và C chứa tên customer và product tương ứng và Orders!$A&$2: $B$2 là bảng tra cứu của bạn trong một bảng tính khác.

Mẹo. Để làm cho công thức dễ đọc hơn, bạn có thể tạo một vùng được đặt tên cho bảng tra cứu, và công thức của bạn sẽ đơn giản hơn :=VLOOKUP(B2&” “&C2,Orders,4,FALSE)

Chú ý. Để công thức hoạt động, cột bên trái nhất của bảng tra cứu của bạn phải chứa các giá trị tra cứu ghép nối chính xác như trong tiêu chí tra cứu của bạn. Như bạn thấy trong hình trên, chúng ta ghép các giá trị bằng một khoảng trắng trong bảng tra cứu, vì vậy chúng ta phải làm tương tự trong các tiêu chí tra cứu của công thức VLOOKUP (B2 & “” & C2).

Ngoài ra, hãy nhớ VLOOKUP giới hạn trong 255 ký tự . Hàm VLOOKUP không thể tìm kiếm giá trị tra cứu có chứa hơn 255 ký tự. Vì vậy, hãy nhớ điều này và đảm bảo tổng chiều dài của tiêu chí tra cứu của bạn không vượt quá giới hạn này.

Hàm Vlookup chỉ có thể lấy một giá trị phù hợp, chính xác hơn, là giá trị đầu tiên được tìm thấy. Thế nếu có nhiều giá trị tra cứu và bạn muốn xuất hiện hai ba lần? Hoặc nhiều hơn nữa, nếu bạn muốn kéo tất cả các giá trị phù hợp? Nghe có vẻ phức tạp, nhưng vẫn có cách thực hiện.

Giả sử bạn có tên khách hàng trong một cột và các sản phẩm họ mua ở một cột khác. Và bây giờ, bạn muốn tìm sản thứ 2 thứ 3 hoặc thứ 4 do cùng một khách hàng mua.

Cách đơn giản nhất là thêm cột phụ trước cột Customer Names và điền tên và thêm số thứ tự vào, ví dụ như “John Doe1”, “John Doe2” vv Công thức COUNTIF dưới đây là mánh nhỏ để làm việc này (giả sử tên khách hàng Nằm trong cột B):

=B2&COUNTIF($B$2:B2,B2)

Sau đó, bạn có thể sử dụng một công thức VLOOKUP thông thường để tìm thứ tự tương ứng. Ví dụ:

=VLOOKUP(“Dan Brown2”,$A$2:$C$16,3,FALSE)

=VLOOKUP(“Dan Brown3”,$A$2:$C$16,3,FALSE)

Đương nhiên, bạn có thể nhập một tham chiếu ô thay vì văn bản trong giá trị tra cứu, như bạn thấy trong ảnh chụp màn hình bên dưới:

Nếu bạn đang tìm kiếm lần xuất hiện thứ 2 , bạn có thể làm mà không cần tạo cột phụ bằng cách tạo ra một công thức VLOOKUP phức tạp hơn:

=IFERROR(VLOOKUP($F$2,INDIRECT(“$B$”&(MATCH($F$2,Table4[Customer Name],0)+2)&”:$C16″),2,FALSE),””)

Trong công thức:

Tham khảo: Phát hiện và xử lý lỗi công thức trong Excel

Chú ý. Công thức này chỉ tìm thấy giá trị phù hợp thứ hai. Nếu bạn cần có sự xuất hiện khác, hãy quay lại với giải pháp trước.

Nếu bạn muốn có được danh sách tất cả các giá trị phù hợp, hàm VLOOKUP không thể làm được, bởi vì nó chỉ có thể trả lại một giá trị tại một thời điểm. Nhưng hàm INDEX của Excel có thể xử lý trường hợp này và tôi sẽ cho bạn thấy công thức trong ví dụ tiếp theo.

Như đã đề cập ở trên, hàm VLOOKUP không thể nhận được bản sao trùng lặp của giá trị tra cứu. Để làm điều này, bạn sẽ cần một công thức mảng phức tạp hơn bao gồm nhiều hàm Excel như INDEX, SMALL và ROW.

Tham khảo:

Hướng dẫn sử dụng hàm Large và hàm Small trong Excel 2007, 2010, 2013, 2016, 2019

Ví dụ, công thức dưới đây tìm tất cả các trường hợp của giá trị trong ô F2 trong phạm vi tra cứu B2: B16, và trả về giá trị từ cột C trong các hàng tương tự:

{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,””), ROW()-3)),””)}

Sao chép công thức dưới đây vào một số ô lân cận, ví dụ: các ô F4:F8 như thể hiện trong ảnh chụp màn hình dưới đây. Số lượng ô mà bạn sao chép công thức phải bằng hoặc lớn hơn số mục trùng lặp tối đa. Ngoài ra, nhớ nhấn Ctrl + Shift + Enter để nhập một công thức mảng một cách chính xác.

Nếu bạn tò mò muốn biết logic của công thức, chúng ta hãy đi tìm hiểu sâu một chút:

Phần 1 .IF($F$2=B2:B16,ROW(C2:C16)-1,””)

$F$2=B2:B16– so sánh giá trị trong ô F2 với mỗi giá trị trong dải B2: B16. Nếu tìm thấy giá trị phù hợp, ROW(C2:C16)-1 trả lại số của hàng tương ứng (-1 được sử dụng để khấu trừ dòng tiêu đề). Nếu các giá trị so sánh không khớp, hàm IF trả về một chuỗi rỗng.

Kết quả của hàm IF là mảng sau đây: {1, “”, 3, “”, 5, “”, “”, “”, “”, “”, “”,12, “”, “”, “”}

Phần 2 .ROW()-3

Trong trường hợp này, hàm ROW hoạt động như một bộ đếm thêm. Vì công thức được sao chép vào ô F4: F9, ta thêm -3 cho hàm trả 1 cho ô F4 (hàng 4 trừ 3), 2 cho ô F5 (hàng 5 trừ 3), vv

Phần 3 .SMALL(IF($F$2=$B$2:$B$16,ROW($C$2:$C$16)-1,””),ROW()-3))

Hàm SMALL trả về giá trị nhỏ nhất thứ k trong một bộ dữ liệu. Trong trường hợp của này, vị trí (từ nhỏ nhất) trở về được xác định bởi hàm ROW (Phần 2). Vì vậy, đối với ô F4, hàm SMALL ({array}, 1) trả về phần tử trước tiên (nhỏ nhất) của mảng, tức là 1. Đối với ô F5, nó trả về phần tử nhỏ thứ 2 của mảng, là 3, trên.

Phần 4 .INDEX($C$2:$C$16, SMALL(IF($F$2=$B$2:$B$16, ROW($C$2:$C$16)-1,””), ROW()-3))

Hàm INDEX chỉ đơn giản trả về giá trị của một ô xác định trong mảng C2: C16. Đối với ô F4, INDEX($C$2:$C$16,1) trả về “Apples”; Cho tế bào F5, INDEX($C$2:$C$16,3) trả về “Sweets”, v.v.

Phần 5 .IFERROR()

Cuối cùng, chúng ta lồng công thức trong hàm IFERROR bởi vì chắc bạn không muốn thấy thông báo N / A trong bảng tính của bạn, khi số ô mà bạn đã sao chép công thức lớn hơn số lần xuất hiện trùng lặp của giá trị tra cứu.

Tìm kiếm 2 chiều trong Excel (còn gọi là tra cứu ma trận hoặc tra cứu hai chiều) tìm kiếm trên cả hàng và cột . Nói cách khác, bạn tìm kiếm một giá trị tại giao điểm của một hàng và cột nhất định.

Vì vậy, hãy sử dụng bảng “Monthly Sales” một lần nữa và viết công thức VLOOKUP tìm xem bao nhiêu lemons được bán vào tháng 3.

Bạn có thể thực hiện tra cứu hai chiều bằng một vài cách khác nhau. Vì vậy, hãy xem qua các lựa chọn thay thế dưới đây.

Bạn có thể sử dụng liên kết của các hàm VLOOKUP và MATCH để tham chiếu chéo hai trường trong cơ sở dữ liệu, trong ví dụ này là Product (hàng) và Month (cột):

=VLOOKUP(“Lemons”,$A$2:$I$9,MATCH(“Mar”,$A$1:$I$1,0),FALSE)

Công thức ở trên là một hàm Vlookup thông thường tìm kiếm kết hợp chính xác của “Lemons” trong các ô từ A2 đến I9. Tuy nhiên, vì bạn không biết chính xác cột bán hàng của tháng ba (Mar), bạn không thể cung cấp số cột trong tham số thứ ba của công thức VLOOKUP của bạn. Thay vào đó, bạn sử dụng chức năng MATCH để tìm cột đó.

Xem thêm: Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel

Hàm MATCH(“Mar”,$A$1:$I$1,0) có nghĩa là:

Đây là cách bạn tạo ra một công thức tra cứu có hai tiêu chí trong Excel, nó còn được gọi là tra cứu hai chiều.

Hàm SUMPRODUCT nhân các thành phần trong các mảng nhất định và trả về tổng của chúng:

=SUMPRODUCT(($A$2:$A$9=”Lemons”)*($A$1:$I$1=”Mar”),$A$2:$I$9)

Hàm INDEX & MATCH

Công thức là:

=INDEX($A$2:$I$9, MATCH(“Lemons”,$A$2:$A$9,0), MATCH(“Mar”,$A$1:$I$1,0))

Xem thêm: Hàm SUMPRODUCT trong Excel và một số ví vụ công thức

Nếu bạn không thích những công thức Excel phức tạp, bạn có thể dùng cách dễ nhớ này:

1. Chọn bảng của bạn, chuyển sang tab Formulas và nhấp vào Create from Selection.

2. Microsoft Excel sẽ tạo ra các tên từ các giá trị trong hàng trên cùng và cột bên trái của lựa chọn của bạn, và bạn sẽ có thể tìm kiếm các tên đó trực tiếp thay vì tạo thành một công thức .

3. Trong bất kỳ ô rỗng nào, gõ =row_value column_value, ví dụ =Lemons Mar,hoặc ngược lại = Mar Lemons.

Hãy nhớ tách giá trị hàng và giá trị cột của bạn với khoảng trắng, trong trường hợp này chúng sẽ hoạt động như một toán tử giao nhau.

Khi bạn gõ, Microsoft Excel sẽ hiển thị một danh sách các tên phù hợp, chính xác như khi bạn bắt đầu gõ một công thức.

Tất cả các cách, tùy theo phương pháp bạn chọn, kết quả tra cứu hai chiều cũng sẽ giống nhau:

Đôi khi bảng chính và bảng tra cứu của bạn không có một cột chung nào, điều này không cho bạn thực hiện một Vlookup bình thường. Tuy nhiên, có 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 của bạn và một chung với bảng tra cứu của bạn.

Hãy xem xét ví dụ sau đây. Bạn có bảng chính với một cột đơn, New SKU, và bạn cần phải kéo giá tương ứng từ một bảng khác. Ngoài ra, bạn có 2 bảng tra cứu – mã đầu tiên chứa mã số New SKU và tên sản phẩm tương tự, cái thứ hai liệt kê tên sản phẩm, giá cả, nhưng có Mã SKU old.

Để kéo giá từ bảng Lookup 2 đến bảng Chính, bạn phải thực hiện cái gọi là vlookup đôi của Excel, hoặc vlookup lồng nhau.

=VLOOKUP(A2,New_SKU,2,FALSE)

Trường hợp ‘New_SKU’ là phạm vi được đặt tên cho $A:$B trong ” Lookup table 1 “ và ” Lookup table 2″ , thì cột B có chứa tên sản phẩm (xin xem hình trên).

=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

Trong đó Price là một phạm vi được đặt tên $A:$C trong bảng tra cứu 2, và 3 là cột C chứa giá.

Hình dưới đây cho thấy kết quả trả về bởi công thức vlookup lồng nhau của chúng ta:

Dưới đây Blog Học Excel Online sẽ kết hợp một số hàm trong Excel để tạo ra một hàm tổng hợp có chức năng dò tìm và trả về nhiều giá trị. Video và bài thuyết trình sau đây sẽ giúp các bạn hiểu được công thức mảng tương đối phức tạp này. Lưu ý, sau khi nhập công thức, các bạn phải sử dụng tổ hợp phím CTRL + SHIFT + ENTER để nhập công thức mảng này.

Ứng dụng của cách tra cứu kiểu này là tạo ra phiếu xuất kho, đã được trình bày trong video ở bài viết này

Download tài liệu kèm theo video

Để bắt đầu, hãy xác định chính xác “kéo dữ liệu động từ các trang tính khác nhau” có nghĩa là để đảm bảo rằng chúng nằm trên cùng một trang.

Trong một số trường hợp, bạn có thể có dữ liệu ở cùng một định dạng được chia trên nhiều bảng tính và mỗi lần bạn muốn kéo dữ liệu phù hợp từ một trang tính cụ thể tùy thuộc vào giá trị được nhập vào một ô nào. Một ví dụ sẽ giúp ta dễ hiểu hơn.

Giả sử bạn có một vài báo cáo bán hàng khu vực cho cùng một sản phẩm ở cùng một định dạng và bạn muốn tìm số bán cho một khu vực nhất định:

Nếu bạn chỉ có một vài khu vực, bạn có thể sử dụng một công thức VLOOKUP khá đơn giản với một Hàm IF để chọn trang cho vlookup:

=VLOOKUP($D$2,IF($D3=”FL”,FL_Sales,CA_Sales),2,FALSE)

Trong đó:

Tuy nhiên, nếu bạn có nhiều bảng tra cứu, hàm IF không phải là giải pháp lý tưởng. Thay vào đó, bạn có thể sử dụng hàm INDIRECT để trả về phạm vi tra cứu cần thiết.

Hàm INDIRECT được sử dụng để gián tiếp tham khảo một ô, và đây chính xác là điều chúng ta cần lúc này. Vì vậy, hãy tiếp tục và thay thế câu lệnh IF bằng tham chiếu INDIRECT theo công thức trên. Và đây là công thức khi kết kết hợp VLOOKUP và INDIRECT trong ví dụ:

=VLOOKUP($D$2,INDIRECT($D3&”_Sales”),2,FALSE)

Trong đó:

“_Sales” là phần chung của tên phạm vi, hoặc tên bảng. Kết nối với giá trị trong ô D3 nó tạo tên đầy đủ của dãy yêu cầu. Dưới đây tôi sẽ cung cấp một số chi tiết cụ thể hơn cho những người không có nhiều kinh nghiệm với hàm INDIRECT của Excel.\

Trước hết, hãy để tôi nhắc cho bạn cú pháp của hàm INDIRECT: =INDIRECT (ref_text, [a1])

Tham số đầu tiên có thể là tham chiếu ô theo kiểu A1 hoặc kiểu R1C1, tên dải ô, hoặc một chuỗi văn bản. Thông số thứ hai xác định loại tham chiếu được chứa trong ref_text – kiểu A1 (TRUE hoặc bỏ qua) hoặc kiểu R1C1 (FALSE). Đó là A1 trong trường hợp của chúng ta, vì vậy chúng ta có thể bỏ qua tham số thứ hai và chỉ tập trung vào cái đầu tiên.

Bây giờ, hãy trở lại với báo cáo bán hàng của chúng ta. Như bạn thấy, mỗi báo cáo là một bảng riêng biệt ở trong một trang riêng biệt. Để công thức hoạt động, bạn cần phải đặt tên cho các bảng hoặc dãy của bạn, và tất cả các tên nên có một phần chung. Ví dụ: báo cáo bán hàng của ta có tên: CA_Sales, FL_Sales, TX_Sales vv Như bạn thấy, luôn có phần _Sales.

Vì vậy, Hàm INDIRECT INDIRECT ($D3& “_ Sales”) kết nối giá trị trong cột D với từ _Sales (với một gạch dưới), và cho hàm VLOOKUP biết chính xác bảng để tra cứu. Nghĩa là, nếu bạn có FL trong ô D3, công thức sẽ tìm kiếm trong bảng FL_Sales, nếu CA – thì trong bảng CA_Sales, v.v …

Kết quả được tạo ra bởi hàm VLOOKUP và INDIRECT của bạn sẽ giống như sau:

Nếu dữ liệu của bạn nằm trong các bảng tính nhau, bạn sẽ phải thêm một tên bảng tính trước vùng được đặt tên (WorkbookName! NamedRange), ví dụ:

=VLOOKUP($D$2,INDIRECT($D3&”Workbook1!_Sales”),2,FALSE)

Chú ý. Nếu hàm INDIRECT đề cập đến một workbook khác, bảng tính đó phải được mở. Nếu bảng tính nguồn không mở, công thức INDIRECT của bạn sẽ trả về lỗi #REF!.

Để nâng cao kiến thức Excel, bạn hãy tham gia ngay khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Trong khóa học này bạn sẽ được tìm hiểu cách sử dụng các công cụ: conditional formatting, data validation, pivot table… để ứng dụng vào trong công việc. Ngoài ra bạn còn biết cách sử dụng các hàm trong excel, từ các hàm cơ bản cho tới các hàm nâng cao, phức tạp. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online

Hàm Vlookup nâng cao trong Excel xuất phát từ hàm Vlookup thông thường nhưng được kết hợp với một số hàm khác để giải quyết những tình huống nâng cao mà hàm Vlookup thông thường không giải quyết được. Bản chất hàm Vlookup nâng cao là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc (theo cột) giúp chúng ta thống kê, dò tìm dữ liệu theo cột một cách nhanh chóng và tiện lợi. Trong bài viết này, Học excel cơ bản sẽ giúp bạn hiểu và sử dụng hàm Vlookup một cách thành thạo qua các ví dụ thực tế nhất.

Keywords searched by users: vlookup excel nâng cao

Categories: Tìm thấy 59 Vlookup Excel Nâng Cao