Cách sử dụng hàm VLOOKUP – Từ cơ bản – Nâng cao (PRO)

Click ngay để download miễn phí

Bạn được học về: Cách sử dụng hàm vlookup, Video hướng dẫn, Download 6 Bài tập thực hành có lời giải. Học cách kết hợp với hàm Left, Match, IF, Value, ISNA, Right, Mid, And, Columns,…

ham vlookup cach su dung trong excel 2003 2007 2010 2013 co ban toi nang cao tu chuyen gia hàm vlookupAd sẽ hướng dẫn các bạn từ những kiến thức cơ bản tới nâng cao về hàm này. Bởi lẽ đó là một trong những hàm PHẢI BIẾT của bất kỳ ai muốn sử dụng excel để học tốt và làm việc thành công.

Bài viết có lượng kiến thức khá lớn từ cơ bản tới nâng cao. Do đó sẽ khá dài và là một trong những bài viết có độ sâu – chất lượng nhất hiện nay về hàm vlookup. Mục tiêu của ad là bạn không cần phải tìm kiếm đâu nữa để có thể thành thạo được hàm này.

Do đó, Hãy lưu lại bài viết để đọc bất kỳ khi nào cần hoặc quên một điểm  nào đó. Và chia sẻ cho bạn bè mình cùng biết.

  • Lưu lại bài viết trên trình duyệt: Các bạn có thể lưu lại bài viết bằng cách đánh dấu bài viết vào trình duyệt (nhấn Ctrl + D nếu là chrome) để mở lại bài viết khi cần xem lại nhé.
  • Lưu lại trên Facebook: Copy link bài viết rồi đăng lên Facebook để lưu lại xem khi cần

 

Bây giờ thì có thể bắt đầu hành trình Trở thành Master về hàm tìm kiếm theo cột này rồi.

1. Cú pháp, cách sử dụng hàm vlookup trong excel

  • Định nghĩa:

Hiểu một cách đơn giản là hàm tìm kiếm giá trị dựa trên điều kiện cho trước (tìm kiếm theo cột) trong excel.

Trong excel có nhiều hàm kiếm tìm kiếm tuy nhiên hàm này là hàm được sử dụng nhiều nhất.

  • Cú pháp hàm:

ham vlookup cach su dung trong excel 2003 2007 2010 2013 co ban toi nang cao tu chuyen gia 1

Trên đây là cú pháp của hàm bằng cả tiếng anh và tiếng việt. Tại sao ad lại viết cả cú pháp tiếng Anh và tiếng Việt, bởi lẽ khi các bạn dùng excel đa phần là ta dùng giao diện bằng tiếng anh. Và đặc biệt hơn là từ excel 2007 trở đi mỗi khi ta viết hàm thì excel sẽ có phần gợi nhớ tên hàm và cú pháp của hàm đó bằng tiếng anh. Do vậy, khi bạn hiểu rõ về hàm bằng cả Anh và Việt thì khi làm việc thực tế các bạn không gặp trở ngại nào và hiệu suất làm việc là cao nhất.

Cú pháp hàm gồm có 4 thành phần:

Ví dụ ngắn gọn sau đây sẽ giúp bạn hiểu nhanh về hàm. Bên cạnh đó các ví dụ và bài tập ở phía dưới sẽ giúp bạn hiểu sâu và biết nhiều cách vận dụng hàm trong thực tế làm việc.

ham vlookup 1

Yêu cầu: Tìm kiếm phụ cấp của từng nhân viên dựa vào Chức vụ của người đó và Bảng phụ cấp.

Gợi ý: Dùng hàm tìm kiếm theo cột để tìm kiếm phụ cấp của từng nhân viên từ Bảng Phụ cấp có sẵn. Bảng phụ cấp gồm 2 phần:

  • Cột số 1: Các chức vụ có trong doanh nghiệp: GĐ, PGĐ, TP, PP, NV
  • Cột số 2: Phụ cấp tương ứng với từng chức vụ.

Như vậy ta xác định được các thành phần sau:

Chức vụ của nhân viên đang muốn tìm phụ cấp

Điều kiện tìm kiếm

Bảng phụ cấp là vùng ô H3:I7

Vùng tìm kiếm

Là cột số 2 -> Cột chứa giá trị phụ cấp trong bảng phụ cấp

Số thứ tự cột chứa giá trị tìm kiếm

0 – là kiểu tìm kiếm chính xác 100%.

Kiểu tìm kiếm



Bên cạnh đó còn có kiểu tìm kiếm là 1 (gần đúng) -> với thành phần này các bạn không cần phải quá quan tâm tới nó nhé. Chỉ cần biết trên 99% các nhu cầu tìm kiếm khi sử dụng hàm này thì thành phần này đều là 0.

TRÌNH TỰ THỰC HIỆN

  • Bước 1: Nhập công thức hàm tìm kiếm theo logic ở trên vào ô D3 với điều kiện tìm kiếm là giá trị của ô C3
  • Bước 2: Copy công thức ở ô D3 xuống các ô còn lại của cột phụ cấp trong bảng tính lương. Như vậy chỉ mất vài giây là bạn có thể tìm được phụ cấp cho hàng trăm, nghìn nhân viên rồi.

Công thức Nhập công thức ở ô D3 như sau:

= Vlookup (C3,$H$3:$I$7,2,0) => Hàm vlookup sẽ trả về giá trị phụ cấp của GĐ là 50.000 đ.

Chắc hẳn bạn sẽ hỏi tại sao lại có dấu $ trong phần vùng tìm kiếm phải không nào?

Điểm này rất quan trọng, không chỉ với hàm vlookup đâu nhé các bạn.

Ý nghĩa của dấu $: Một cách ngắn gọn đó là việc cố định vùng tìm kiếm để khi copy công thức ở ô D3 xuống cho các nhân viên còn lại trong bảng lương thì vùng tìm kiếm này không thay đổi. Điều này cho phép hàm vlookup tính toán 1 cách chính xác mà không phải viết đi viết lại hàm khi tìm kiếm phụ cấp cho từng nhân viên.

Còn nếu bạn muốn hiểu tận tường về ý nghĩa, cách dùng của dấu $ thì hãy xem ngay bài viết này nhé.

ham vlookup 2

Giải thích cơ chế hoạt động của lệnh vlookup:

Khi viết hàm như trên, excel sẽ tự động tìm trong cột Chức vụ của bảng chức vụ dòng nào chứa giá trị của ô C3 (GĐ). Khi tìm được dòng đó rồi excel sẽ tự động di chuyển sang 2 cột (số cột ta chỉ định trong hàm vừa viết) tính từ cột mã hàng và lấy giá trị ở ô vừa di chuyển tới để trả kết quả về ô ta viết công thức.

Đương nhiên là sau khi nhấn enter rồi.

Copy công thức của ô đầu tiên xuống các ô còn lại:

  • Copy công thức ở ô D3 (nhấn Ctrl+C)
  • Bôi đen các ô còn lại: Vùng ô D4:D12
  • Dán công thức, nhấn Ctrl + V

Như vậy, chỉ với 2 thao tác sao chép – dán: Bạn có thể tìm được phụ cấp của hàng trăm nghìn nhân viên trong nháy mắt rồi.

Video hướng dẫn cách sử dụng hàm vlookup siêu dễ hiểu và ngắn gọn

 

Bài viết tham khảo thêm

Trên đây chỉ là một ví dụ đơn giản và ngắn gọn giúp bạn biết được định nghĩa và cú pháp của hàm này. Để hiểu rõ và sâu hơn về hàm này các bạn hãy xem một bài viết khác sau đây:

ham vlookup co ban

Với bài viết này, các bạn sẽ biết những kiến thức cơ bản nhất như định nghĩa, cấu trúc hàm và ví dụ đơn giản về cách sử dụng. Ngoài ra bài viết cũng cung cấp cho bạn cả video hướng dẫn sử dụng hàm để tìm số dư trong kế toán.

Click thẳng vào ảnh để lấy link ảnh >> Copy >> Paste vào Facebook nhé bạn.

cách sử dụng hàm vlookup, nâng cao, nhiều điều kiện, trong excel 2007 2010 2013

 

2. Bài tâp hàm vlookup với 2 cấp độ: Cơ bản và Nâng cao

Đương nhiên là Học phải đi đôi với hành, khi bạn biết cách sử dụng rồi thì phải có bài tập để thực hành thì mới mong thành thạo được. Trong bài viết này ad mới chỉ upload 3 bài tập: 1 cơ bản và 2 nâng cao để các bạn thực hành trước và sẽ up nhiều hơn trong thời gian tới. Nói gì thì nói khi học một cái mới cũng như đi cái xe máy mới, phải chạy roda đã nên hãy làm bài tập cơ bản. Ngon rồi thì làm tiếp bài tập nâng cao. Sau đó tìm các bài tập khác trên Excel.webkynang.vn để làm tiếp – Link download

2.1. Bài tập cơ bản về sử dụng hàm vlookup trong excel

Ad chọn giới thiệu tới các bạn bài tập này là bởi vì nó vừa có yêu cầu cơ bản vừa có yêu cầu nâng cao đối với hàm tìm kiếm theo cột này. Bên cạnh đó quan trọng hơn cả là có cả video bài giải, như vậy khi gặp khó trong lúc làm bài tập bạn sẽ có phần hướng dẫn cụ thể để phát hiện ra mình sai ở đâu.

Ngoài ra trong video hướng dẫn này, ad còn hướng dẫn một vài thủ thuật nhỏ nhưng lại rất hữu ích với những bạn mới sử dụng excel. Ad hi vọng rằng các bạn sẽ hiểu được và thành thạo cách vận dụng hàm vlookup cơ bản để xử lý các yêu cầu thực tế.

Click để xem và tải bài tập cơ bản

2.2. Bài tập nâng cao

Trong phần này, ad giới thiệu tới các bạn 2 bài tập ngắn nhưng lại yêu cầu kỹ năng vận dụng hàm kết hợp tìm kiếm với các hàm khác. Đây là dạng bài tập thách thức khả năng xử lý linh hoạt tình huống thay vì vận dụng hàm một cách dập khuôn.

Click để tải bài tập nâng cao

Gợi ý giải bài tập:

Trong trường hợp bạn bí trong việc giải 2 bài tập nâng cao này thì hãy xem phần gợi ý dưới đây nhé:

  • Bài 1: Sử dụng hàm vlookup để Tìm Đơn giá nhập/ xuất

ham vlookup de tim kiem don gia

Thực chất bài tập này chỉ khó ở phần tìm Đơn giá nhập/ xuất của từng giao dịch phát sinh dựa trên cột Mã hàng.

Ký tự cuối của mỗi mã hàng là: N hoặc X tương ứng với Nhập hàng hoặc Xuất hàng. Và oái oăm là ta lại có 2 bảng đơn giá: 1 bảng đơn giá nhập, 1 bảng đơn giá xuất.

Để giải được bài tập này các bạn phải xác định được từng thành phần của hàm vlookup thì mới có thể giải được.

Mã hàng -> Cần phải tách được chữ N hoặc X khỏi mã hàng trong bảng kê nhập xuất

Điều kiện tìm kiếm

Mã hàng -> Dựa vào ký tự cuối của mã hàng trong bảng kê nhập xuất để xác định

Vùng tìm kiếm

Ở cả 2 bảng đơn giá thì phần giá đều nằm ở cột thứ 2

Cột chưa giá trị đang tìm kiếm

0 – đương nhiên là loại tìm kiếm chính xác 100% rồi.

Loại tìm kiếm

 

Các hàm tham khảo để giải bài này:

If – hàm nếu thì

– Vlookup – hàm tìm kiếm theo cột

Len – hàm đếm số ký tự

Left – hàm tách ký tự từ một chuỗi ký tự

  • Bài 2: Tìm tên vùng của số điện thoại

Bài này nhìn thì ngắn nhưng mà độ hóc và xương của nó thì cũng không hề đơn giản đâu nhé các bạn. Cái khó ở bài này là điều kiện tìm kiếm có thể là 2, 3 hoặc 4 ký tự (mã vùng) trong khi số điện thoại lại phải có nghĩa và lại cũng gồm nhiều loại: 9 và 10 số.

Để giải bài này bạn hãy khoanh vùng danh sách mã vùng và dùng hàm if để loại bỏ tất cả các trường hợp gây trùng và lẫn. Đương nhiên đây là hàm if lồng rồi. Bạn hãy xem ví dụ về hàm if lồng nếu bạn chưa từng dùng nó bao giờ nhé.

3. Hàm vlookup kết hợp hàm IF

Trong excel, việc kết hợp các hàm excel sẽ tạo nên những công thức excel rất mạnh nhằm phục vụ công việc tìm kiếm và tính toán dữ liệu. Và sự kết hợp giữa vlookup và if là một ví dụ điển hình mà bạn phải biết.

Ở phần bài tập nâng cao phía trên ad có đề cập tới việc kết hợp 2 hàm này để xác định vùng tìm kiếm tùy thuộc vào ký tự cuối của mã hàng trong bảng kê nhập xuất là N hay X.

Thật đơn giản, ta chỉ cần thay thế một vùng tìm kiếm thông thường bằng một hàm if cho phép tăng tính linh hoạt của vùng tìm kiếm. Và ta sẽ sử dụng hàm vlookup kết hợp hàm if để giải quyết yêu của bài nâng cao trên như sau:

= Vlookup ([Mã hàng], If ([Ký tự cuối] = “N”, [Bảng đơn giá nhập], [Bảng đơn giá xuất]), 2, 0)

Trên đây là một ví dụ điển hình về sự kết hợp giữa vlookup và if. Còn bài tập dưới đây bạn hãy download về thực hành xem sao nhé. Vì đây là một ứng dụng rất thực tế của việc vận dụng excel vào công việc thực tế nên ad nghĩ rằng các bạn hãy nghiêm túc học và thực hành hắn nhé.

Xem bài chi tiết về sự kết hợp với hàm IF 

4. Hàm vlookup kết hợp hàm LEFT

Trong bài viết này ad chỉ giới thiệu với các bạn cách kết hợp với 1 trong các hàm xử lý chuỗi trong excel đó là hàm left. Ngoài hàm left, ta còn có thể kết hợp với hàm: Mid, Right, Trim,…

Nhưng chỉ cần học cách kết hợp với hàm left là cũng ổn rồi. Khi thành thạo rồi ta sẽ biết cách kết hợp hàm vlookup với các hàm còn lại. Nên các bạn không cần phải lo lắng quá.

Còn về cách sử dụng các hàm xử lý chuỗi trong excel. Nếu bạn chưa biết cách sử dụng thì chỉ tìm kiếm ngay trên webkynang là có.

Trong bài này ad sẽ không nêu lại cách kết hợp giữa 2 hàm này. Vì ad đã có hẳn một bài viết chi tiết và khá dài để giải thích cặn kẽ. Hãy đọc để hiểu từng li từng tí để bạn hiểu rõ bản chất cũng như có bài tập để bạn thực hành.

Xem bài hướng dẫn chi tiết cách kết hợp hàm LEFT

5. Hàm vlookup kết hợp hàm match trong excel (Vlookup nâng cao)

Kiểu kết hợp này là một kiến thức nâng cao và nếu bạn không hiểu được ngay lần đọc đầu tiên thì cũng không phải lo lắng nhé. Bạn có thể xem chi tiết về định nghĩa, cú pháp và cách sử dụng về hàm match trong excel tại bài viết này. Trong bài viết về hàm match, ad cũng đã thêm cả ví dụ, giải thích cách sử dụng kết hợp match và vlookup. Thêm vào đó là bạn có thể download file mẫu bài tập nâng cao tại bài viết đó.

ham vlookup ket hop ham match

Ad giới thiệu qua về sự kết hợp này.

  • Hàm match là hàm giúp tìm số thứ tự của 1 giá trị trong một danh sách
  • Hàm vlookup có một thành phần rất quan trọng đó là số thứ tự của cột chứa giá trị đang tìm tính từ cột đầu tiên của vùng tìm kiếm.
  • Kết hợp match và vlookup với nhau ta có thể tiết kiệm được rất nhiều thời gian khi điều kiện tìm kiếm bị qui định bởi cả dòng và cột.

Cụ thể như trong bài ví dụ nâng cao về hàm match: Tên xe được qui định bởi Tên hãng và Số phân khối. Hàm tìm kiếm theo cột giúp tìm tên xe dựa vào tên hãng, còn hàm match lại tìm kiếm số thứ tự cột dựa trên số phân khối của xe cần tìm tên.

ham match trong excel ket hop ham vlookup index hàm match kết hợp hàm index vlookup 1

6. Kết hợp với hàm columns, column

Đến đây thì ai cũng hiểu và phải hiểu rằng, hàm vlookup là hàm tìm kiếm theo cột. Trong đó có thành phần thứ 3 của cú pháp là số thứ tự cột chứa giá trị muốn tìm tính từ cột đầu tiên của vùng tìm kiếm.

Vậy nếu cột chứa giá trị đang tìm có số thứ tự thứ mấy chục (50+, 100+, 10.000 + cột) thì bạn sẽ ngồi đếm như thế nào.

Ad đảm bảo rằng, bạn mà đếm bằng mắt sẽ tiềm ẩn 2 rủi ro cực cao:

  • Chắc chắn là đếm rất lâu, tốn thời gian
  • Độ chính xác và tin tưởng không được cao lắm.

Giải pháp: Thật may mắn ta có thể sử dụng kết hợp hàm tìm kiếm theo cột với hàm xác định số thứ tự cột trong excel.

  • Hàm column (địa chỉ ô): Là hàm excel để tìm số thứ tự của một ô cho trước tính từ cột A
  • Hàm columns (địa chỉ vùng): Là hàm excel để tìm số thứ tự của một cột tính từ cột đầu tiên của vùng dữ liệu.

Khi đó hàm tìm kiếm này sẽ có cú pháp như sau:

= Vlookup (Điều kiện, Vùng điều kiện, Columns (…), 0)

Thủ thuật này rất hữu ích đó các bạn độc giả. Hãy thực hành và vận dụng luôn nhé.

ham vlookup ket hop columns column

7. Ví dụ về cách sử dụng hàm vlookup để tìm kiếm dữ liệu từ sheet khác

Trong các bức ảnh phía dưới: Đây là file Tính lượng tiêu thụ nhiên liệu và ta có 2 sheet:

  • Sheet “Bang tieu thu”
  • Sheet “DANH SACH”

7.1. Sheet “Bang Tieu Thu”

Trong bảng [Bảng tiêu thụ nhiên liệu] ta đã có Mã Hàng và chúng ta cần tìm

  • Tên hàng
  • Hãng sản xuất cùng với
  • Đơn giá

Để hoàn tất Bảng tiêu thụ nhiên liệu.

ham vlookup trong excel tren nhieu sheet 0

7.2. Sheet “DANH SACH”

Có 2 bảng giúp ta lấy được toàn bộ dữ liệu cần ở sheet “Bảng Tiêu Thụ”

  • Bảng [Danh sách Hãng]:

Cung cấp tên hãng tương ứng với mã hãng là 2 ký tự sau cùng cũng Mã Hàng.

  • Bảng [Danh sách mặt hàng]:

Cung cấp Tên Hàng và Đơn giá từng mặt hàng. Tìm theo Mã Hàng là Ký tự đầu tiên bên trái của Mã Hàng trong [Bảng Tiêu Thụ Nhiên Liệu]

ham vlookup trong excel tren nhieu sheet 1

7.3. Cách sử dụng hàm vlookup để tìm Tên nhà sản xuất từ sheet “DANH SACH”

Bước 1: Nhập công thức vào ô đầu tiên trong cột hãng sản xuất như sau:

ô D3 = Vlookup (RIGHT (B3,2),

Như phân tích ở trên bên sheet “Danh sach” ở bảng Hãng sản xuất có 2 cột Mã Hãng và Tên Hãng. Trong đó Mã hãng được quyết định bởi 2 ký tự cuối cùng bên phải của Mã Hàng.

Do đó ta phải sử dụng hàm RIGHT ở trường hợp này.

Nhớ là phải đánh cả dấu “,” để nói cho excel biết ta đã xong phần nhập điều kiện tìm kiếm, giờ là chuyển sang phần chọn Vùng tìm kiếm nhé.

ham vlookup trong excel tren nhieu sheet 1

Bước 2: Dùng chuột để chọn vùng tìm kiếm

Dùng chuột để chọn vùng tìm kiếm ở bên sheet “Danh sách” – Bảng [DANH SÁCH HÃNG].

Click chuột vào tab tên sheet.

Sau đó chọn toàn bộ bảng [DANH SÁCH HÃNG]

Bạn lưu ý nhé, trong quá trình chọn vùng dữ liệu, vùng được chọn sẽ được bao quanh bởi đường gạch đứt như hình vẽ nhé.

Tương ứng với công thức như sau:

ô D3 = Vlookup (RIGHT(B3,2), ‘DANH SACH’ ! B3:C8

ham vlookup trong excel tren nhieu sheet 1

Giải thích vùng điều kiện của hàm vlookup:

‘DANH SACH’! đại diện cho tên sheet

B3:C8: địa chỉ của Bảng [DANH SÁCH HÃNG]

Sau bước này bạn cũng cần nhập dấu “,” sau C8 để chuyển sang cột chỉ định của công thức vlookup nhé.

Lưu ý:

Sau khi chọn vùng tìm kiếm xong bạn cần cố định vùng tìm kiếm bắng phím F4. Như vậy khi copy công thức thì vùng dữ liệu không bị thay đổi theo.

Click để Xem bài viết: Ý nghĩa của việc cố định vùng dữ liệu

Bước 3: Hoàn tất hàm vlookup.

Ta chọn cột chỉ định để tìm Tên hãng sản xuất = 2

Kiểu tìm kiếm là tìm kiếm chính xác = 0.

Công thức được viết hoàn chỉnh như sau:

ô D3 = Vlookup (RIGHT (B3,2), ‘DANH SACH’ ! $B$3:$C$8,2,0)

ham vlookup trong excel tren nhieu sheet 1 Sử dụng hàm vlookup

Bước 4: Copy công thức ở ô D3 xuống cho các ô còn lại là sẽ tìm được tên Hãng sản xuất cho các mặt hàng phía dưới.

Tuy nhiên bạn để ý sẽ thấy mặt hàng số 6 “D00TN” bị lỗi #N/A

Bạn hãy xem bài viết sau để biết rõ lý do vì sao nhé:

Lỗi #N/A trong hàm tìm kiếm theo cột vlookup

 

Nhiệm vụ của bạn là hãy áp dụng hướng dẫn trên để tìm Tên Hàng và Đơn giá. Sau đó hoàn tất Bảng Tiêu Thụ Nhiên Liệu.

7.4 Sử dụng hàm vlookup để tìm kiếm dữ liệu từ file khác.

Các bước thực hiện bạn làm tương tự với tìm kiếm dữ liệu từ Sheet khác.

  • Nhập tên hàm
  • Viết thành phần đầu tiên của hàm: Điều kiện tìm kiếm
  • Dùng chuột để chọn vùng tìm kiếm ở file khác
  • Hoàn thiện hàm vlookup bằng loại tìm kiếm

Thật quá đơn giản phải không nào các bạn.

Lưu ý để có thể dùng chuột chọn vùng dữ liệu từ file khác bạn cần mở xong xong cả 2 file lên nhé.

8. Lỗi #N/A khi dùng hàm vlookup là lỗi gì?

Khi sử dụng hàm excel để tìm kiếm, tính toán không phải lúc nào cũng ra kết quả như ý, nhiều lần trong số đó là trả về giá trị lỗi. Điển hình trong khi sử dụng hàm này bạn sẽ gặp lỗi #N/A, vậy đây là lỗi gì và cách phục như thế nào.

Gặp lỗi #N/A khi trong công thức có sử dụng hàm Left, Right, Mid

Important:

 

Mời bạn xem bài viết về lỗi #N/A

Xem cách xử lý lỗi #N/A

Sơ qua về lỗi:

Đây không phải là một lõi nghiêm trọng.

Và thường nó không chứng minh là bạn viết sai hàm. Nhưng nếu bạn copy công thức vừa viết cho các ô còn lại trong cột mà kết quả trả về đều là #N/A thì khả năng bạn viết sai là rất cao.

Bản chất của lỗi này là excel không tìm thấy điều kiện tìm kiếm trong vùng tìm kiếm.

Thường sử dụng kết hợp hàm IF và ISNA để bẫy lỗi #N/A

Important:

Có thể bạn chưa biết:

N/A có thể là [Not Available] hoặc [No Answer] hoặc [Not Applicable]  – hay nhỉ, vừa được học excel vừa được học tiếng anh. 🙂

9. Lỗi #REF khi sử dụng hàm vlookup là gì?

Đây là một lỗi ít khi gặp phải khi sử dụng hàm tìm kiếm theo cột hoặc theo hàng (hlookup). Tuy nhiên nếu gặp phải thì rất nhiều người không biết cách xử lý ra sao. Nhưng bạn đừng lo, khi đã đến với ad thì bạn sẽ hiểu rõ nguyên nhân và cách xử lý lỗi này một nhanh – gọn – hiệu qủa.

  • Nguyên nhân:

Do số thứ tự cột/ dòng chứa giá trị tìm kiếm lớn hơn tổng số cột vùng tìm kiếm. Số cột đó tính từ cột điều kiện tới cột/dòng cuối cùng của vùng tìm kiếm

  • Giải pháp:

Mở rộng vùng tìm kiếm của hàm vlookup (Tương tự cho hàm hlookup)

Trong trường hợp vùng tìm kiếm của bạn bị giới hạn từ cột A -> D: Tổng số cột trong vùng là 4. Và trong hàm vlookup bạn chỉ định cột chứa giá trị tìm kiếm có số thứ tự là 5 thì excel không thể hiểu được. Vì 5 lớn hơn cả tổng số cột của vùng tìm kiếm rồi đúng không nào.

Biết bệnh rồi, giờ hãy bắt tay vào sửa lại hàm mình vừa viết thôi.

10. Hàm tìm kiếm với 2 hay nhiều điều kiện (dùng công thức mảng)

Bạn có bao giờ phải đối mặt với việc phải tìm kiếm dữ liệu mà có tới vài điều kiện tìm kiếm không.

Trên thực tế, đây là một yêu cầu khó với hầu hết người dùng Excel. Do vậy nếu bạn nằm trong số đó cũng không có gì phải tự ti bạn nhé.

Chỉ cần xem ví dụ ad đưa ra trong Hình ảnh dưới đây. Bạn sẽ biết cách làm thế nào để dùng hàm vlookup tìm kiếm với 2 hay nhiều điều kiện trong excel.

ham vlookup 2 dieu kien nhieu dieu kien trong excel cong thuc mang

Trong ví dụ trên đây, ta phải tìm kiếm Doanh số theo 2 điều kiện là Mã hàng & Khu vực.

Để sử dụng được hàm mảng bạn không nhấn Enter mà dùng Ctrl + Shift + Enter

Important:

Hiểu đơn giản ví dụ trên như sau:

Và ad dùng công thức mảng cho trường hợp này, cùng với đó là hàm Vlookup kết hợp hàm Choose. Hàm choose có nhiệm vụ lần lượt chọn 2 điều kiện là Mã hàng & Khu vực để tạo thành 1 cặp điều kiện.

Còn lại nhiệm vụ của hàm vlookup và công thức mảng là quét cả vùng để tìm lấy giá trị tương ứng mà hàm choose vừa ghép được.

Chắc hẳn rất nhiều bạn đọc về ví dụ này sẽ thấy thật khó hiểu. Không hiểu sao lại làm được thế đúng không nào.

Bạn đừng quá lo lắng, công thức mảng là một kiến thức khó trong excel. Ngay cả ad cũng chỉ là A mA Tơ mà thôi. Và bạn có thể download file ví dụ vừa rồi về để xem cách ad làm nhé.

Link download hàm tìm kiếm nhiều điều kiện

11. Sử dụng hàm vlookup để tìm kiếm nhiều từ 2 điều kiện trở lên dùng cột phụ

Có thể với hầu hết người dùng excel, công thức mảng là một thứ xa lạ và rất khó dùng.

Thực tế thì Trường cũng ít khi dùng công thức mảng, chỉ khi nào gặp ca khó mới dùng tới nó mà thôi.

Do vậy, Trường sẽ hướng dẫn các bạn cách xử lý yêu cầu tìm kiếm theo nhiều điều kiện một cách đơn giản. Không yêu cầu phải biết dùng công thức mảng như sau:

Trong ví dụ ở phần 10 phía trên, yêu cầu là phải tìm doanh số theo 2 điều kiện: Mã hàng và Khu Vực.

Ta có thể dễ dàng xử lý được yêu cầu bằng cách tạo cột phụ. Sau đó gom 2 điều kiện trên thành một.

Tức là:

Cột Điều kiện tìm kiếm = {Mã hàng} & {Khu vực}

Khi đó điều kiện tìm kiếm gồm 2 thành phần là Mã hàng & Khu vực sẽ tương ứng với cột phụ ta vừa tạo.

Do đó công thức hàm vlookup lại trở về dạng đơn giản nhất rồi phải không nào.

Bạn chỉ cần Xem hình ảnh dưới đây để hiểu cách sử dụng hàm tìm kiếm vlookup nhiều điều kiện.

ham-vlookup-2-dieu-kien

 

12. Tải 6 bài tập hàm vlookup cơ bản tới nâng cao 

Các bạn click đường link dưới đây để tải.

Link tải bài tập

Lưu ý:

Các bạn lưu ý, đây là các phần thực hành yêu cầu sự tự giác cao của người học. Do vậy, nếu có gặp khó khăn trong cách giải cũng không được nản.

Bạn cần phải cố gắng suy nghĩ và tự đặt ra các câu hỏi tại sao cho từng bài.

Sau khi nghĩ khá lâu mà không ra cách làm thì bạn hãy mở đáp án ra để xem. Đó là các sheet có cùng số thứ tự với bài đó nhưng có thêm “.1” ở sau trong tên sheet.

Trường hợp xấu nhất là ngay cả khi xem đáp án mà vẫn không biết cách làm. Bạn cần làm thế nào?

Bạn hãy email về cho ad theo địa chỉ: Webkynang.vn@gmail.com để ad hỗ trợ nhé.

13. Vận dụng hàm vlookup trong Kế toán excel

Vlookup trong kế toán excel được vận dụng như thế nào? Trường sẽ giới thiệu tới các bạn cách vận dụng hàm tìm kiếm vào một số sổ sách báo cáo kế toán excel không thể thiếu được.

13.1. Dùng để tìm kiếm dữ liệu cho phiếu thu – phiếu chi

Mọi phiếu thu chi đều có mã số Phiếu thu – Phiếu chi.

Các thông tin trong phiếu thu – chi đều được nhập vào sheet data của file kế toán excel.

Như trong bức hình dưới đây, Ta chọn số phiếu chi “PC04/12” ở ô R4. Khi đó mọi thông tin liên quan đến số phiếu chi này đều tự động được hàm vlookup tìm kiếm ở dữ liệu nguồn để điền thông tin vào:

=> Tham khảo mẫu phiếu thu – chi trên excel

Ham vlookup trong ke toan excel 0 Hàm vlookup trong kế toán excel

13.2. Lập bảng cân đối số phát sinh/ bảng cân đối tài khoản

Trong kế toán excel hay trong bất kỳ phần mềm kế toán nào, các danh mục (tài khoản, nhà cung cấp, nhân viên,…) là phần không thể thiếu được. Dựa trên thông tin khai báo tại các danh mục này, chúng ta dùng hàm vlookup để tìm kiếm thông tin liên quan để điền vào các sổ, báo cáo,…

Cú pháp hàm tại cột B:

= Vlookup (Số tài khoản, Danh mục tài khoản, STT cột tên tài khoản, 0)

Ham vlookup trong ke toan excel 0 

13.3. Tìm tên tài khoản ở các sổ chi tiết, sổ cái trong kế toán excel

Sổ cái trong kế toán excel nên được thiết kế trên một sheet thay vì nhiều sheet.

Đây là điểm khác biệt với file được nhiều trung tâm đào tạo kế toán excel đang sử dụng để dạy. Họ thường mở mỗi sheet một sổ. Sau đó copy chết giá trị thay vì để công thức linh hoạt – tự động cập nhật khi có thay đổi.

Chính bởi vì tính linh hoạt trong việc để tất cả sổ cái trên cùng 1 sheet. Khi đó ta cần sử dụng hàm vlookup để tìm tên của từng số tài khoản được chọn.

Ham vlookup trong ke toan excel 0

13.4. Hàm vlookup để lập bảng lương

Mỗi công ty đều có một danh sách nhân viên khác nhau với nhiều thông tin đi kèm với từng nhân viên. Trong khi đó, bảng lương chỉ chứa một số thông tin nhất định về nhân viên và còn lại là thông tin về lương, phụ cấp của nhân viên. Thông tin về ngày công thường được gắn với tên hoặc mã nhân viên, trong bảng lương ta sẽ dễ dàng tìm được các thông tin về lương cơ bản, mức phụ cấp, … để tính các phần khác của bảng lương.

Tất cả là nhờ có hàm vlookup – hàm tìm kiếm theo cột

Ham vlookup trong ke toan excel 0

13.5. Tìm tên nhà cung cấp trong sổ theo dõi công nợ chi tiết

Tương tự, như việc sử dụng hàm vlookup để tìm thông tin của từng nhân viên. Trong bảng tổng hợp chi tiết công nợ, ta cũng cần sử dụng để tìm kiếm tên nhà cung cấp, có thể cả địa chỉ, hệ số tín nhiệm, kỳ hạn nợ,…

Ham vlookup trong ke toan excel 0

13.6. Vlookup để tìm thông tin cho phiếu nhập kho – phiếu xuất kho

Trong phiếu nhập kho – phiếu xuất kho, ta đều cần phải biết được hàng hóa được nhập từ ai hoặc xuất tới kho nào, chỗ nào. Ta dùng hàm vlookup để tìm các thông tin này từ bảng kê nhập xuất hàng hóa và các danh mục tương ứng.

Ham vlookup trong ke toan excel 0

Trên đây là một số ứng dụng của hàm vlookup trong kế toán excel. Trên thực tế, còn nhiều yêu cầu khác cần đến khả năng tìm kiếm thông tin của hàm này mà Trường không thể đề cập hết ngay được.

Trường sẽ update dần dần và các bạn hãy luôn theo dõi blog của Trường nhé.

14. +20 lưu ý quan trọng khi sử dụng hàm VLOOKUP trong excel

Hiện tại Trường mới kịp cập nhật tới lưu ý thứ 5, do bận quá nên phải up từ từ. Trường sẽ hoàn thiện dần dần để phục vụ các bạn nhé.

14.1. Điều kiện tìm kiếm không phân biệt chữ hoa – chữ thường

Khi điều kiện tìm kiếm là:

  • Webkynang.vn
  • Hay WEBKYNANG.VN

Thì kết quả mà vlookup tìm được không khác gì nhau các bạn nhé.

14.2. Có 2 kiểu tìm kiếm khi dùng hàm vlookup

Kiểu số 1: 0 / False – tìm kiếm chính xác

Kiểu số 2: 1 / True – tìm kiếm tương đối

Thông thường, tìm kiếm chính xác hay được dùng hơn. Và ta dễ dàng kiểm soát được kết quả theo ý ta.

Còn kiểu 2 thì khá khó dùng với nhiều người.

14.3. Khi tìm kiếm theo kiểu tương đối, danh sách tìm kiếm phải được sắp xếp

Nhiều bạn, dùng điều kiện tương đối để tìm kiếm.

Nhưng lại không sort và tự vò đầu bứt tai rõ ràng là hàm viết đúng sao lại không ra kết quả.

Khi đó bạn cần check ngay xem mình đã sắp xếp lại danh sách theo kiểu ascending nhé.

14.4. Vlookup chỉ tìm kiếm từ trái qua phải

Đây là điểm mấu chốt khi dùng hàm mà bạn phải biết.

Đó là lý do tại sao cột đầu tiên bên trái của vùng tìm kiếm phải là cột chứa điều kiện tìm kiếm

Để tìm ngược lại từ phải qua trái bạn có 2 cách:

14.5. Chỉ tìm được giá trị đầu tiên xuất hiện

Ví dụ thực tế là khi ta lập phiếu xuất kho/ nhập kho.

Trong bảng kê nhập/ xuất, một số phiếu có thể xuất hiện nhiều lần.

Thông thường, người dùng chỉ biết dùng hàm vlookup để tìm kiếm. Nhưng hàm này không đáp ứng được yêu cầu của phiếu xuất/ nhập do chỉ tìm được giá trị của dòng đầu tiên mà số phiếu ta cần tìm xuất hiện.

Giải pháp đưa ra:

Kết hợp nhiều hàm với nhau: If, Type, Match, Offset

Bạn có thể tham gia khóa học excel 1 thầy 1 trò để được hướng dẫn cụ thể hơn khi cần nhé.

Để luôn cập nhật kiến thức về Excel – Bạn nên đăng ký nhận bài viết mới qua email nhé:

Click để đăng ký

Lưu ý: Sau khi đăng ký, bạn hãy vào email vừa đăng ký để xác nhận đăng ký. Có thể thư kích hoạt bị rơi vào hòm thư spam. Bạn hãy vào hòm thư spam để kiểm tra nhé.

Click ngay

28 Comments

  1. Kimtuyen
    • Admin
    • HOANG ANH
      • Admin
        • Tuan
      • Hưng Võ
        • Admin
        • hung
          • Admin
          • hung
          • Thanh Phong
          • QUANG
          • Admin
  2. Nguyễn Trung
    • Admin
  3. ĐỒng
    • Admin
  4. QUANG
  5. phạm thành luận
    • Admin
  6. gà đồi
    • Admin
  7. Hà Thuỷ
    • Admin
  8. Phượng
  9. Nguyen Huy

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.