Cách sử dụng hàm VLOOKUP trong excel - cùng với những ví dụ hữu dụng.

() translation by (you can also view the original English article)

VLOOKUP trong Excel là gì?

VLOOKUP là từ viết tắt của " tìm kiếm theo chiều dọc" là một công thức trong Microsoft Excel để tìm ra dữ liệu trùng khớp từ hai danh sách. Thay vì phải nhảy qua lại giữa hai bảng tính và gõ vào những dữ liệu tương ứng, bạn có thể viết ra công thức Vlookup để tự động quy trình này.

Combining two lists is a a great situation to use VLOOKUP — Kết hợp hai danh sách là một cách hoàn hảo để sử dụng hàm VLOOKUP.

Ở phía bên trái (trong hình bên trên), chúng ta có thông tin nhân viên trả về. Chúng ta sẽ thêm vào job title của nhân viên vào dữ liệu trả về. Cùng với một danh sách riêng biệt của nhân viên và chức vụ của họ, chúng ta có thể viết một công thức VLOOKUP để điền vào vị tiêu đề từ một danh sách tìm kiếm.

Một hàm LOOKUP thành công cần 3 thứ:

Một giá trị tham chiếu trong mỗi danh sách mà bạn có thể dùng để tìm kiếm dữ liệu trùng khớp. Hai danh sách này cần phải có ít nhất một phần dữ liệu giống nhau (Trong ví dụ VLOOKUP trong Excel bên trên thì đó là ID của nhân viên).
Một danh sách tra cứu, bao gồm "cơ sở dữ liệu" của bạn hay là những thông tin cơ bản (danh sách chức vụ nhân viên).
Dữ liệu, bạn sẽ muốn dùng chúng để điền vào những phần trùng khớp (dữ liệu trả về)

Ví dụ ngắn gọn của công thức VLOOKUP trong Excel được thực hiện.

Hàm VLOOKUP là một công thức của Microsoft Excel, cần thiết để làm việc với những tập hợp dữ liệu lớn. Trong bài hướng dẫn này, tôi sẽ chỉ cho bạn làm thế nào để hiểu và sử dụng chúng.

Example VLOOKUP formula used to look up employee data — Ví dụ về công thức Vlookup được dùng để tìm kiếm dữ liệu nhân viên.

Sử dụng ví dụ ở trên, tôi sẽ viết ra một công thức VLOOKUP để có thể tìm kiếm employee's ID và điền vào phần Job title các dữ liệu trả về. Bởi vì hai sheet này đều có cột Employee ID, nên Excel có thể tìm kiếm phần Job Title tương ứng. Phần tuyệt vời nhất của hàm VLOOKUP đó là tôi có thể kéo cùng một công thức xuống dưới, và chúng sẽ tìm kiếm giá trị Job Title độc lập.

Tải bảng tính Excel miễn phí

Trước khi tiếp tục, hãy đảm bảo rằng bạn đã tải bản đính kèm miễn phí. Đây là ví dụ bài tập mà tôi đã tạo ra để chúng ta có thể sử dụng trong suốt bài hướng dẫn này.

Xem và học: VLOOKUP

Cách nhanh nhất để học công thức VLOOKUP cơ bản là hãy tham khảo bản screencast bên dưới. Video này đi qua một vài ví dụ công thức hàm VLOOKUP, sử dụng bài tập mẫu.

Tiếp tục đọc để đến phần hướng dẫn viết, và học thêm một vài các kỹ năng khác không có trong screencast.

Làm thế nào để sử dụng hàm VLOOKUP trong Excel: theo dõi hết chu trình này.

Sử dụng tab "Ingredient Orders" và "Supplier List" cho ví dụ VLOOKUP trong Excel này.

Giả dụ chúng ta quản trị một nhà hàng và chúng ta đặt hàng với cá nhà cung cấp hàng tuần. Đầu bếp trưởng đưa cho chúng ta một danh sách nguyên liệu cần đặt hàng và chúng ta cần đưa vào thông tin về nhà cung cấp.

Đây là 3 thông tin mà chúng ta cần thêm vào cho mỗi đơn hàng.

Supplier
Supplier Phone
Supplier Delivery Day

Trong workbook này, có hai tab:

Ingredient Orders - bao gồm danh sách những yêu cầu nguyên liệu từ đầu bếp trưởng.
Supplier List - bao gồm những thông tin về các nhà cung cấp như tên nhà cung cấp và số điện thoại.

Excel VLOOKUP list ingredient order and supplier list example — Ở phía trên cùng, chúng ta có dữ liệu đơn hàng, trong đó có nhãn "Ingredient" Bên dưới là "danh sách Supplier", được dùng như danh sách tìm kiếm.

Trường thông tin chung giữa hai bảng này đó là nhãn Ingredient. Chúng ta sẽ sử dụng nhãn này để tìm kiếm một trong ba trường và thêm vào danh sách đơn hàng.

Bước 1: Bắt đầu với công thức VLOOKUP trong Excel.

Tại tab Ingredient Orders, hãy click vào phần trống đầu tiên trong ô Supplier, chọn F5 và gõ vào dấu = để bắt đầu công thức VLOOKUP. Tiếp theo, gõ vào "VLOOKUP( " để bắt đầu công thức.

=VLOOKUP(

Hãy nhớ rằng giá trị tham chiếu - là những thông tin xuất hiện trong cả hai danh sách - đó là ingredient, do đó chúng ta sẽ dùng chúng để tìm kiếm. Có thể click vào ô B5 hoặc gõ vào đó công thức. Tiếp theo, thêm vào dấu phẩy sau "B5", chúng ta có thể thêm vào phần tiếp theo của công thức.

=VLOOKUP(B5,

Bây giờ, chúng ta cần đưa vào công thức danh sách tìm kiếm. Cùng với công thức vẫn đang mở, click vào nhãn Supplier List. Tiếp theo, click vào ô A3, click và kéo chuột để tô đen và chọn từ ô A3 đến G13, toàn bộ bảng dò tìm. Kiểm tra kỹ và nhấn phím F4 trên bàn phím của bạn để làm cho công thức một giá trị tuyệt đối (sẽ nói rõ hơn sau). Cuối cùng thêm vào một dấu phẩy khác.

Select the Excel lookup list — Dùng con trỏ để chọn danh sách tìm kiếm

Sau khi thêm vào dấu phẩy trong ô tìm kiếm, chuyển sang các nhãn và điểm Excel trong danh sách tìm kiếm. Click và kéo ô A3 đến G3 để chọn dữ liệu dò tìm. Đảm bảo là nhấn F4 từ bàn phím trong suốt bước này để tạo ra giá trị tuyệt đối, chúng sẽ khóa lại các ô để sử dụng cho việc tìm kiếm.

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,

Tiếp theo, chúng ta sẽ cần nói với Excel là cột nào sẽ cần lấy dữ liệu. Hãy nhớ tằng những món đầu tiên thêm vào trong tên Supplier nằm trong cột thứ 2 của danh sách tìm kiếm. Thêm số 2 vào công thức để trích dẫn cột thứ hai của vùng tìm kiếm, và thêm vào một dấu phẩy khác nữa.

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,

Cuối cùng, chúng ta sẽ thêm vào FALSE để tìm kiếm chính xác, sau đó đóng ngoặc:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,FALSE)

Pulldown formula is working perfectly in Excel — Công thức của chúng ta hoạt động một cách hoàn hảo, giá trị dò tìm trong cột Supplier cho Potatoes.

Tin tốt là chúng ta không cần phải viết lại công thức này nhiều lần - chỉ cần nhấp đôi chuột vào phần góc dưới bên phải của ô F5 (bạn có thể thấy ô vuông màu xanh lá nhỏ nằm ở góc của ô) để mở rộng phần công thức xuống phía dưới (đã cho thấy bên trên)

Công thức làm việc một cách hoàn hảo! Bây giờ thì chúng ta sẽ tiếp tục trích xuất dữ liệu trong hai trường còn lại: supplier phone number và delivery day.

Bước 2: Trích lục dữ liệu với VLOOKUP.

Bởi vì chúng ta đã sử dụng giá trị tuyệt đối, về cơ bản, chúng ta có thể sử dụng lại cùng một công thức mà chúng ta đã viết với một chút thay đổi nhỏ. Tiếp theo, chúng ta sẽ thêm vào Supplier Phone Number.

Chúng ta sẽ bắt đầu bằng việc sao chép và dán ô F5 (ô Supplier) vào ô G5. Tôi chỉ sử dụng phím tắt Ctrl + C và Ctrl + V để sao chép và dán toàn bộ ô đó. Ban đầu, chúng sẽ không hoạt động, và bạn sẽ thấy ký tự N/A trong ô.

Copying and pasting formula in Excel Lookup Moved — Sao chép và dán giá trị tìm kiếm với sự điều chỉnh trong Excel.

Sẽ dễ dàng hơn rất nhiều để sao chép và dán công thức vào một ô khác, nhưng nó đòi hỏi một chút chỉnh sửa. Đầu tiên, Excel sẽ nhìn vào ô C5 để tìm kiếm, nhưng chúng ta cần thay đổi nó thành "B5" trong thanh công thức. Một khi chúng ta thực hiện điều đó, việc tìm kiếm sẽ hoạt động - hầu như là thế.

Chúng ta sẽ phải trở lên phía trên thanh công thức và thay đổi phần đầu tiên của công thức từ C5 trở lại thành B5. Khi chúng ta di dời công thức đó sang một cột khác, Excel sẽ cập nhật những phần khác của công thức. Chúng ta sẽ có một "N/A" bởi vì Excel đang cố gắng để trùng khớp số lượng (Cột C) của danh sách tìm kiếm, nhưng danh sách tìm kiếm lại không bao gồm những số lượng đó.

Cho đến thời điểm này, công thức của chúng ta sẽ là:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,FALSE)

Tuy nhiên, hãy chú ý màn hình chụp ở trên rằng chúng ta đang trích xuất sai dữ liệu vào cột "Phone" Chúng ta vẫn đang trích xuất cột thứ 2 trong danh sách tìm kiếm bởi số "2" trong công thức. Chúng ta cần thay đổi nó thành số thứ tự cột của supplier's contact phone.

Bước 3: Sửa lỗi VLOOKUP N/A

Chúng ta sẽ quay lại và kiểm tra danh sách tìm kiếm. Bạn sẽ thấy rằng giá trị phone number hiện tại đang nằm ở cột thứ 7 trong bảng tìm kiếm. Chúng ta sẽ quay lại công thức Excel và cập nhật giá trị trích xuất từ cột thứ 7.

Lookup List Column numbers need to be updated — Chúng ta cần cập nhật số "2" trong công thức Excel thành số "7" để trích xuất từ cột thứ 7 trong danh sách tìm kiếm.

Tất cả chúng ta cần làm là cập nhật vùng cột của danh sách dò tìm từ "2" thành "7" và bây giờ chúng đã cho kết quả tốt.

Our Excel vlookup formula is Updated for Supplier Phone — Chú ý rằng supplier phone đang hoạt động hiệu quả và chúng ta sẽ cập nhật công thức này để sử dụng cho cột thứ 7 từ danh sách dò tìm. Bây giờ, chúng ta chỉ cần kéo công thức xuống để cập nhật cho tất cả các ô.

Công thức cuối cùng cho việc tìm kiếm giá trị supplier phone number như sau:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,7,FALSE)

Bước 4: Thêm vào một cột khác để hoàn thành công thức VLOOKUP.

Cuối cùng, chúng ta sẽ theo tác trên cột Supplier Delivery Day. Sao chép và dán chúng từ ô G5 vào ô H5. Một lần nữa, chúng ta sẽ sửa lại công thức này bằng cách thay đổi C5 thành B5 để sử dụng cột ingredient như là giá trị tham chiếu. Sau đó, chỉ cần cập nhật "7" thành "5" để sử dụng cột thứ 5 từ bảng dò tìm.

Final supplier delivery day lookup formula applied — Cuối cùng, công thức tìm kiếm dữ liệu Supplier DeliverDay đã hoàn thành.

Công thức hoàn thành cho cột supplier delivery day:

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,5,FALSE)

Thế là xong. Chúng ta đã viết một công thức cơ bản và chỉnh sửa một chút để đưa vào các dữ liệu mà chúng ta cần để đưa vào cho yêu cầu tiếp theo.

Xử lý sự cố VLOOKUP

Theo từng bước hướng dẫn, bạn đã viết được công thức VLOOKUP, nhưng nó vẫn chưa đúng. Excel đòi hỏi mọi thứ thật chính xác, do đó chúng ta cần cẩn thận với dữ liệu và công thức VLOOKUP. Chúng ta sẽ xem vài trường hợp sửa đúng lại công thức VLOOKUP mà nó chưa làm việc nhé.

1. Nhiều dữ liệu trùng khớp.

Một trong những lỗi phổ biến của hàm VLOOKUP đó là khi có quá nhiều trùng khớp trong danh sách dò tìm. Khi bạn sử dụng hàm VLOOKUP, chúng sẽ chỉ trích ra thông tin đầu tiên trùng khớp trong danh sách dò tìm.

Troubleshooting Example of Excel VLOOKUP not working — Theo hàm VLOOKUP của chúng ta thì nhân viên Carrie Richard giữ chức vụ Director of Marketing, nhưng cô ấy gần đây đã được thăng chức vào vị trí Company President. Tai sao dữ liệu dò tìm của chúng ta không chính xác?

Lookup problem with two listings conflicting in our VLOOKUP — Ôi, dường như chúng ta có liệt kê cho Carrie Richard trong danh sách tìm kiếm - Một cho Direct of Marketing, và một cái cho President. Khi chúng ta xóa "Director of Marketing" từ danh sách dò tìm, dữ liệu của chúng ta đã chính xác rồi đấy.

Công việc của hàm VLOOKUP là tìm kiếm dữ liệu trùng khớp dựa vào một danh sách. Danh sách tìm kiếm đó sẽ không chứa giá trị dùng để dò tìm giống nhau, dữ liệu mà bạn dùng để trùng khớp. Mặt khác, Excel sẽ bị lẫn lộn và chỉ chọn cho bạn dữ liệu đầu tiên trùng khớp.

2. Khoảng trắng trước và sau

Một lỗi phổ biến khác nữa đó là dữ liệu của chúng ta có thể không thật sự trùng khớp theo cách nhìn của Excel. Dữ liệu với một khoảng trắng nằm trước nó cho ta hiểu đó là khoảng trắng "trước", trong khi dữ liệu với khoảng trắng theo sau nó là một "khoảng trắng sau".

Trailing Space VLOOKUP Example Problem — Công thức VLOOKUP này đã được viết ra hoàn hảo, tuy nhiên chúng không hoạt động. Bạn có thể thấy rõ tên trong danh sách dò tìm, nhưng Excel không trả lại một kết quả chính xác. Đọc phần này để tìm hiểu lý do nhé.

Nếu những dữ liệu trùng khớp có một khoảng trắng trước và sau nó, Excel sẽ xem như hai dữ liệu này hoàn toàn khác nhau, và sẽ không trả về giá trị trùng khớp. Đối với Excel, " Andrew", "Andrew " và "Andrew" là 3 dữ liệu độc lập không trùng khớp trong hàm VLOOKUP.

Resolving Excel VLOOKUP Trailing Space Issue — Hãy bỏ đi "khoảng trắng sau" hoặc khoảng trắng đơn lẻ phía sau cột tên trong ô. Mặc dù ta không thấy được, nhưng chúng có thể làm hỏng một công thức VLOOKUP bởi vì Excel xem ""Alyssa Reddall" and "Alyssa Reddall(khoảng trắng)" là khác nhau. Một khi bạn đã xóa được khoảng trắng đó, hàm VLOOKUP sẽ cho ra kết quả chính xác.

Nếu dữ liệu của bạn có khoảng trắng trước và sau, sử dụng chức năng TRIM trong Excel để bỏ nó đi. Hàm VLOOKUP sẽ hoạt động tuyệt vời sau khi sử dụng chức năng này để bỏ đi khoảng trắng trước và sau.

3. Khóa ô tham chiếu.

Có thể bạn đã biết được thao tác kéo một ô xuống bằng phương pháp thủ công để dán công thức vào những ô khác. Tuy nhiên, đôi khi chức năng này cũng làm hỏng hàm VLOOKUP bởi vì ô tham chiếu thay đổi.

Bad reference VLOOKUP problem — Trong màn hình chụp này, tôi sẽ kéo hàm VLOOKUP xuống danh sách để áp dụng công thức vào những ô khác. Tuy nhiên, hãy lưu ý rằng phần tham chiếu tìm kiếm đã thay đổi từ A2 đến B15, từ A16 đến B29, đó là lý do tại sao công thúc đó không hoạt động. Danh sách tra cứu của tôi thật sự là từ ô A2 đến B15, nên việc tra cứu đã bị sai.

Khi kéo những công thức xuống dưới, chúng sẽ kéo phần tham chiếu cho bảng tra cứu ra khỏi trật tự. Đột nghiên, bảng tham chiếu sẽ bị mất đi những dòng từ bảng tham chiếu và hàm VLOOKUP sẽ không hoạt động đúng.

Ta sẽ dùng giá trị tuyệt đối absolute reference để sửa chữa lỗi này, do đó, khi bạn kéo công thức xuống, danh sách chỉ định sẽ không bị thay đối. Click vào ô mà bạn đã viết hàm VLOOKUP, và sau đó click vào vị trí bất kỳ trong phần tham chiếu danh sách giá trị dò tìm. Sau đó, nhấn F4. Bạn sẽ thấy phần công thức này sẽ bao gồm cả những ký hiệu đô-la.

Updated Excel VLOOKUP Formula with Absolute Reference — Chú ý phần công thức (đã chỉ ra ở phía trên của màn hình chụp trong thanh công thức) hiện tại đã bao gồm các ký hiệu đô-la trong phần tham chiếu danh sách tra cứu, đó là do phần này đã sử dụng **tham chiếu tuyệt đố**i. Bây giờ, khi tôi kéo công thức này xuống, nó sẽ khóa công thức cho phần danh sách tra cứu và hoạt động một cách hoàn hảo.

Tóm tắt và tiếp tục trau dồi kiến thức.

VLOOKUP là một trong những công thức cần thiết để trở thành một người sử dụng Excel hiệu quả. Đơn giản là chúng ta không có nhiều thời gian cho việc tìm kiếm dữ liệu và gõ lại hết lần này đến lần khác, do đó, những công thức như VLOOKUP này rất quan trọng để học.

Nếu bạn muốn học kỹ năng sắp xếp nâng cao trong Excel, hãy xem bài hướng dẫn của Bob Flisser's về 12 kỹ năng tạo sức mạnh cho người dùng.
Thêm vào đó, khóa học của Bob về Giới thiệu Spreadsheets là một hướng dẫn hoàn hảo cho người bắt đầu sử dụng Excel.
Đôi khi, ta cũng có thể tìm hiểu các bài học tương tự từ một nguồn tài khác. Tài liệu chính thức của Microsoft Office cho công thức VLOOKUP là một nguồn tuyệt vời khác để sử dụng thành thạo VLOOKUP.

Nếu bạn có bất cứ khó khăn nào trong việc sử dụng công thức VLOOKUP, hãy để lại bình luận bên dưới để chúng ta cùng tháo gỡ nhé.