Sử dụng Excel hoặc Google Trang tính đủ lâu và bạn chắc chắn gặp phải thuật ngữ VLOOKUP. Chính xác thì nó là gì và nó làm gì? Trong hướng dẫn này, chúng tôi thảo luận về chức năng của VLOOKUP, cách bạn có thể sử dụng nó để làm cho công việc của mình dễ dàng hơn và cung cấp cho bạn hướng dẫn về cách sử dụng tốt nhất nó trong cả Excel và Google Trang tính.
Chúng tôi cũng giải quyết các câu hỏi khác nhau xung quanh hàm VLOOKUP và các cạm bẫy phổ biến mà bạn có thể gặp phải khi cố gắng sử dụng lệnh.
VLOOKUP là gì?
VLOOKUP - viết tắt của “vertical lookup” - là một hàm có nguồn gốc trong Microsoft Excel cho phép bạn tìm kiếm một giá trị cụ thể trong một cột, sau đó sử dụng thông tin đó để kéo lên một giá trị khác dọc theo cùng một hàng.
Ví dụ:bạn có thể có ba cột được gắn nhãn “tên,” “số” và “địa chỉ” và các cột này có thể chứa đầy thông tin. Sử dụng hàm VLOOKUP, bạn sẽ có thể tìm kiếm một tên cụ thể trong cột “tên”, sau đó sử dụng thông tin đó để hiển thị một số hoặc địa chỉ tương ứng có thể được tìm thấy cùng hàng với tên đó. Tuy nhiên, hãy nhớ rằng hàm VLOOKUP không phân biệt chữ hoa chữ thường.
Mặc dù nó có vẻ không đặc biệt hữu ích khi được sử dụng trong một nhóm dữ liệu nhỏ như trong ví dụ đã đề cập ở trên, nhưng nó rất hữu ích khi bạn có một lượng lớn thông tin trên trang tính của mình và bạn muốn sử dụng các giá trị nhất định trong các lĩnh vực khác.
Bạn có thể tạo danh sách thông tin chính trong một trang tính, sau đó chỉ cần sử dụng hàm VLOOKUP trong các trang tính kế tiếp để lấy dữ liệu từ danh sách chính. Bằng cách đó, bạn chỉ cần cập nhật một trang tính và các giá trị sẽ tự động theo sau trên phần còn lại.
Nói một cách đơn giản, một chuỗi VLOOKUP như sau:
=VLOOKUP(
the value you want to lookup,
the range of cells you want to look for it in,
the column number of the value you want to display,
whether you want an exact or approximate match
) Cách sử dụng hàm VLOOKUP trong Excel và Google Trang tính
Thoạt nhìn, chuỗi VLOOKUP có vẻ khó khăn, nhưng thực ra nó đơn giản hơn nhiều so với vẻ ngoài, vì những hướng dẫn chuyên sâu này sẽ hiển thị.
- Bạn sẽ cần một bảng thông tin để rút ra từ đó. Chúng tôi đang sử dụng ý tưởng giống như ví dụ đã cung cấp trước đó và thêm thông tin vào ba cột:Tên, Địa chỉ và Số điện thoại.
- Chúng tôi muốn lấy ra số điện thoại tương ứng của bất kỳ tên nào trong một trường nhất định. Trong trường hợp này, chúng tôi muốn lấy số điện thoại của “Iris Watson”.
- Bắt đầu chuỗi bằng cách nhấp đúp vào ô trống và nhập vào
=VLOOKUP(. Bạn sẽ thấy rằng giá trị đầu tiên được yêu cầu là “lookup_value”. là thông tin bạn sẽ sử dụng để tìm kiếm số điện thoại.
- Vì chúng tôi đã thêm tên “Iris Watson” vào ô trước hàm VLOOKUP, ô đó là ô bạn sẽ sử dụng làm giá trị tra cứu. Chuỗi của bạn hiện sẽ trông giống như sau:
=VLOOKUP(e12,
- Tiếp theo là table_array. Đây là toàn bộ bảng thông tin mà bạn đang lấy dữ liệu từ đó. Chỉ cần đánh dấu toàn bộ bảng nơi chứa dữ liệu của bạn. Ở giai đoạn này, chuỗi của bạn sẽ trông giống như sau:
=VLOOKUP(e12,A1:C5,
- Giá trị thứ ba trong chuỗi của bạn là “col_index_number” hoặc số chỉ mục cột. Đây là số cột mà bạn đang cố gắng trích xuất thông tin. Trong mảng bảng chúng tôi đã chọn, “Tên” sẽ được coi là 1, “Địa chỉ” là 2 và “Số điện thoại” là 3. Vì chúng tôi đang cố gắng VLOOKUP một số điện thoại, chuỗi của chúng tôi trông giống như sau:
=VLOOKUP(e12,A1:C5,3,
- Cuối cùng, phần cuối cùng của chuỗi là bạn có muốn đối sánh chính xác hay đối sánh gần đúng cho giá trị mà bạn đang tìm kiếm hay không. Bạn sẽ cần nhập TRUE cho giá trị gần đúng và FALSE cho chính xác. Trong trường hợp của chúng tôi, chúng tôi đã đi với cái sau. Chuỗi cuối cùng của chúng ta là
=VLOOKUP(E12,A1:C5,3,FALSE)
- Như bạn có thể thấy, VLOOKUP của chúng tôi đã lấy thành công số điện thoại tương ứng cho “Iris Watson.”
Lưu ý :hướng dẫn này sử dụng Microsoft Excel để thực hiện VLOOKUP, nhưng bạn có thể sử dụng phương pháp tương tự với Google Trang tính.
Cách Lọc Dữ liệu Sử dụng Nhiều Tiêu chí
VLOOKUP được thiết kế để chỉ lấy một giá trị duy nhất và có các chức năng khác để tra cứu nhiều nguồn thông tin - điều đó không có nghĩa là bạn không thể sử dụng hàm VLOOKUP để tìm kiếm nhiều tiêu chí. Bằng cách sử dụng cột trợ giúp, bạn có thể tạo các số nhận dạng duy nhất lưu trữ thông tin từ nhiều ô, sau đó điều chỉnh hàm VLOOKUP để tìm kiếm các số nhận dạng duy nhất này.
Điều này hữu ích trong trường hợp bạn có nhiều ô có cùng giá trị. Ví dụ:giả sử bảng của chúng tôi có nhiều ô với tên “Iris Watson”. Trong các trường hợp bình thường, hàm VLOOKUP sẽ chỉ hiển thị “Iris Watson” đầu tiên mà nó tìm thấy trong danh sách, nhưng bạn có thể đang tìm một cái khác.
Thông qua việc sử dụng cột trợ giúp, bạn sẽ có một số nhận dạng duy nhất có thể giúp VLOOKUP phân biệt giữa các “Iris Watsons” khác nhau trong trang tính của bạn.
Đối với ví dụ này, chúng tôi đang sử dụng hàm VLOOKUP để hiển thị địa chỉ thay vì số điện thoại. Chúng tôi cũng đã thêm “Iris Watson” thứ hai vào trang tính với một địa chỉ và số điện thoại khác.
- Điều đầu tiên chúng tôi làm là tạo một cột trợ giúp kết hợp các ô "Tên" và "Số điện thoại" để tạo ra một số nhận dạng duy nhất. Chúng tôi đã sử dụng hàm CONCATENATE, hàm này chỉ đơn giản là kết hợp các chuỗi từ các ô khác nhau. Chuỗi của bạn sẽ giống như sau:
=CONCATENATE(B2," | ",D2)Chúng tôi đã thêm dấu ngoặc kép"|"ký hiệu và một số khoảng trắng giữa tên và số điện thoại để dễ hiểu hơn. Nếu bạn muốn tìm hiểu thêm về chức năng này, hãy xem bài viết của chúng tôi về cách sử dụng CONCATENATE.
- Sau khi bạn nối thành công, hãy nhấp vào góc dưới cùng bên phải của ô được đánh dấu và kéo nó xuống các ô khác trong cột trợ giúp. Điều đó cũng sẽ áp dụng cách ghép tương tự cho chúng.
- Bạn sẽ cần hai trường cho hàm VLOOKUP. Chúng tôi đã thêm các trường tra cứu "Tên" và "Số" cũng như trường "Địa chỉ" sẽ hiển thị kết quả VLOOKUP.
- Những gì chúng tôi muốn làm là để hàm VLOOKUP kết hợp thông tin được cung cấp trong các trường tìm kiếm "Tên" và "Số" theo cách sao cho chúng phù hợp với cùng một định dạng mà chúng tôi đang sử dụng trong cột trợ giúp của chúng tôi. Bằng cách đó, hàm VLOOKUP sẽ có thể phát hiện mã định danh duy nhất trong cột trợ giúp và lấy ra địa chỉ tương ứng.
Đây là chuỗi của chúng tôi trông giống như ban đầu:=VLOOKUP(F9&" | "&F10,
F9 và F10 lần lượt là các trường tìm kiếm “Tên” và “Địa chỉ” của chúng tôi và ký hiệu &hoạt động giống như một nối trong đó nó kết hợp cả hai trường với nhau. Dấu “| “Một phần của chuỗi là cùng một dấu phân chia mà chúng tôi đã sử dụng để nối trong cột trợ giúp của chúng tôi.
- Bây giờ bạn chỉ cần làm theo phần còn lại của công thức VLOOKUP. Bạn sẽ phải chọn mảng bảng, nhập số chỉ mục cột và đặt nó thành đối sánh CHÍNH XÁC. Đây là chuỗi kết thúc của chúng tôi:
=VLOOKUP(F9&" | "&F10,A1:D6,3,FALSE)
- Bạn sẽ thấy rằng chỉ nhập tên hoặc địa chỉ vào các trường tìm kiếm là không đủ. Cả hai đều phải được điền để quá trình tra cứu diễn ra thành công. Dưới đây, bạn có thể thấy hai kết quả VLOOKUP khác nhau vì chúng tôi có hai “Iris Watsons” khác nhau. Bạn sẽ nhận được một kết quả khác tùy thuộc vào số điện thoại bạn nhập vào trường tìm kiếm thứ hai.
Liệu VLOOKUP có tốt hơn INDEX-MATCH không?
Câu hỏi này đã được tranh luận rất nhiều kể từ những ngày đầu của Microsoft Excel. Trước khi trả lời câu hỏi này, điều quan trọng là chúng ta phải hiểu INDEX-MATCH chẵn là gì. INDEX và MATCH là hai hàm riêng biệt thường được kết hợp để tạo ra một hệ thống tra cứu linh hoạt hơn so với hàm VLOOKUP.
Hàm MATCH được sử dụng khi bạn muốn tìm số vị trí của một giá trị nhất định trong một phạm vi ô. Mặt khác, INDEX sử dụng hai định dạng tiềm năng để hiển thị một giá trị từ một mảng bảng hoặc phạm vi ô. Bằng cách kết hợp cả hai, người ta có thể sử dụng hàm MATCH để xác định số vị trí của thông tin được cung cấp, sau đó yêu cầu INDEX sử dụng các số vị trí đó để trả về giá trị.
Cái nào trong số hai cái tốt hơn sẽ cho biết ai đang sử dụng hàm. VLOOKUP thân thiện hơn nhiều đối với người mới và người dùng Excel hoặc Google Trang tính hạng trung, do việc thiết lập và sử dụng dễ dàng hơn nhiều. Tuy nhiên, INDEX-MATCH linh hoạt hơn nhiều và có thể được sử dụng trong nhiều tình huống khác nhau.
Vào cuối ngày, nhiều người biết cách sử dụng hàm VLOOKUP hơn so với INDEX-MATCH, vì vậy, cái trước có lẽ là lựa chọn tốt hơn nếu trang tính sẽ được nhiều người dùng không nâng cao truy cập. Nhưng nếu trang tính dành cho các chuyên gia, thì INDEX-MATCH có lẽ là cách tốt nhất.
Những điều Nên làm và Không nên Khi sử dụng VLOOKUP
Không có gì phải xấu hổ khi mắc một vài sai lầm khi bạn mới làm quen với hàm Vlookup. Hầu hết các cựu chiến binh Excel đã từng ở đó vào một thời điểm nào đó trong cuộc đời của họ. Dưới đây là một số điều cần lưu ý khi dùng thử hàm VLOOKUP.
1. LÀM đảm bảo rằng Lookup_Value là cột đầu tiên trong bảng của bạn.
Hàm VLOOKUP với giả định rằng lookup_value của bạn nằm ở cột đầu tiên trong mảng bảng của bạn. Nó chỉ có thể hiển thị thông tin nếu giá trị đó nằm trong ô kế tiếp từ cùng một hàng. Đặt lookup_value của bạn ở bất kỳ vị trí nào khác với phần đầu và chức năng này sẽ không thành công.
2. ĐỪNG quên sử dụng FALSE cho các kết quả phù hợp chính xác.
Phần cuối cùng của chuỗi VLOOKUP cho phép bạn quyết định xem giá trị là FALSE đối với kết quả khớp chính xác hay TRUE đối với kết quả khớp một phần. Nhiều người dùng nhầm lẫn khi sử dụng TRUE, điều này có thể dẫn đến kết quả không chính xác hoặc họ quên đặt một giá trị nào đó.
3. NÊN kiểm tra kỹ số chỉ mục của cột.
Những gì hàm VLOOKUP hiển thị phụ thuộc nhiều vào việc bạn đặt đúng “col_index_num” khi nhập chuỗi. “Col_index_num” hoặc số chỉ mục cột là giá trị số được đặt cho mỗi cột trong mảng bảng của bạn. Giá trị của cột đầu tiên được coi là 1, cột thứ hai là 2, v.v. Nhập sai giá trị “col_index_num” trong chuỗi của bạn và hàm sẽ hiển thị một kết quả hoàn toàn khác.
4. NÊN sử dụng F4 khi sao chép công thức sang các ô khác.
Một trong những điều hữu ích nhất về hàm VLOOKUP là bạn có thể kéo công thức xuống để sao chép nó qua các ô khác nhau. Vấn đề là, các giá trị được chỉ định trong chuỗi hàm cũng sẽ dịch chuyển xuống dưới, làm hỏng toàn bộ công thức. Để tránh điều này, hãy đặt con trỏ vào các giá trị trong công thức của bạn và nhấn F4 Chìa khóa. Điều này biến chúng thành các giá trị tuyệt đối sẽ không thay đổi khi công thức được sao chép.
Các lỗi VLOOKUP Thông thường và Cách khắc phục Chúng
Lỗi phổ biến nhất mà bạn sẽ gặp khi sử dụng hàm VLOOKUP là lỗi “#NA”, mặc dù vậy, điều đáng chú ý là có nhiều lý do khiến lỗi này xuất hiện.
1. Lookup_value của bạn không nằm trong cột đầu tiên của mảng bảng.
Một trong những hạn chế lớn nhất của hàm VLOOKUP là nó chỉ có thể tìm kiếm các giá trị trong cột đầu tiên của mảng bảng của bạn. Nếu lookup_value của bạn không có ở đó, nó sẽ dẫn đến lỗi #NA. Để khắc phục điều này, bạn có thể điều chỉnh công thức của mình để tham chiếu đến một cột khác hoặc di chuyển các cột của bạn để lookup_value ở đúng vị trí.
2. VLOOKUP không tìm thấy kết quả phù hợp chính xác.
Giá trị cuối cùng trong công thức VLOOKUP là đối số range_lookup mà bạn đặt thành TRUE cho một kết quả gần đúng hoặc FALSE cho một kết quả chính xác. Nếu bạn đặt đối số này thành FALSE và hàm VLOOKUP không thể tìm thấy kết quả khớp chính xác, bạn sẽ gặp phải lỗi #NA.
Nếu bạn hoàn toàn chắc chắn phải có sự trùng khớp trong lookup_value của mình, thì bạn sẽ muốn kiểm tra lại thông tin trong mảng bảng của mình để đảm bảo mọi thứ được định dạng đúng và không có khoảng trắng không mong muốn. Các ký tự không in được cũng có thể ngăn hàm VLOOKUP tìm kiếm các mục một cách chính xác.
3. Số dấu phẩy động quá lớn
Về cơ bản, số dấu phẩy động là số có dấu thập phân. Với hàm VLOOKUP, nếu bạn có một hình có quá nhiều số sau dấu thập phân, bạn sẽ gặp phải lỗi #NA. Khắc phục điều này khá dễ dàng:làm tròn số của bạn tối đa là năm chữ số thập phân và nó sẽ hoạt động. Bạn có thể làm như vậy bằng cách sử dụng ROUND chức năng.
Các câu hỏi thường gặp
1. Điều gì xảy ra khi bạn để trống đối số range_lookup?
Đối số thứ tư trong chuỗi VLOOKUP - phải được đặt thành “TRUE” hoặc “FALSE” - được coi là tùy chọn. Đặt nó thành “TRUE” có nghĩa là hàm VLOOKUP của bạn sẽ tìm kiếm kết quả phù hợp gần đúng, trong khi “FALSE” yêu cầu giá trị phải chính xác. Vấn đề là, nếu bạn để trống đối số này, thì hàm VLOOKUP sẽ mặc định nó là “TRUE”, điều này có thể làm hỏng kết quả mong muốn của bạn.
2. Các lựa chọn thay thế cho việc sử dụng hàm VLOOKUP là gì?
Giải pháp thay thế tốt nhất để sử dụng hàm VLOOKUP sẽ là kết hợp INDEX-MATCH. Tuy nhiên, hãy nhớ rằng sự kết hợp các chức năng này khó học hơn một chút vì bạn phải thành thạo hai chức năng khác nhau và đảm bảo chúng hoạt động bình thường cùng nhau.
3. Bạn có thể tìm kiếm giá trị theo hàng thay vì cột không?
Đúng. Bạn có thể làm như vậy với HLOOKUP hoặc "tra cứu theo chiều ngang" trong Excel và Google Trang tính. Hàm này cho phép bạn tra cứu giá trị từ một hàng cụ thể, sau đó hiển thị giá trị từ một hàng khác nhưng trên cùng một cột.
Nếu bạn muốn giới hạn tìm kiếm của mình trong một hàng hoặc cột, bạn có thể sử dụng hàm LOOKUP.