Hàm VLOOKUP cách sử dụng và ví dụ cụ thể

VLOOKUP là một trong những hàm Excel có ích nhất nhưng lại ít người hiểu về nó. Trong bài viết này, chúng tôi sẽ giúp các bạn hiểu rõ về hàm VLOOKUP bằng các thí dụ thực tế, như xếp loại học sinh, chuyên viên bằng cách sử dụng hàm VLOOKUP.

• Đây là những hàm cơ bản nhất trong Excel mà bạn phải nắm rõ

Hàm VLOOKUP là gì?

Hàm VLOOKUP là hàm dò tìm dữ liệu trong Excel. Tôi giả định rằng bạn đã có kiến thức nền về Excel và có thể sử dụng những hàm cơ bản như SUM, AVERAGE, và TODAY. Một trong các chức năng phổ biến nhất của VLOOKUP là một hàm dữ liệu, nghĩa là nó sẽ hoạt động dựa theo những bảng cơ sở dữ liệu hoặc đơn giản hơn là bản kê các hạng mục. Danh sách có biết bao loại. Bạn có thể lập bảng về nhân sự trong công ty, các loại mặt hàng sản phẩm, bản kê khách hàng, hoặc bất kỳ thứ gì rồi cũng được. Và dưới này là bản kê những mặt hàng của dịch vụ A đang bán trên thị trường:

Bảng cơ sở dữ liệu thường sẽ có những triệu chứng nhận biết cho từng sản phẩm. Ở bảng dữ liệu trên, triệu chứng thực biết đặc biệt là cột “Item Code” (Mã sản phẩm). Chú ý: Để cũng có thể sử dụng hàm VLOOKUP thì bảng dữ liệu phải có cột chứa dấu hiệu nhận biết như mã hoặc ID, và buộc phải là cột trước mắt như bảng trên.

VLOOKUP có lẽ là hàm nổi tiếng nhất trong Excel, nhưng vì cả lý do tốt và xấu. Về mặt tốt, hàm VLOOKUP rất dễ sử dụng và xong xuôi được điều gì đó rất hữu ích. Đặc biệt, đối với những người dùng mới, việc coi hàm VLOOKUP quét bảng, tìm một kết quả thích hợp và trả về một kết quả chính xác là điều cực kì thích thú. Sử dụng thành thạo hàm VLOOKUP là một chuyên môn cần thiết, từ người mới bắt đầu đến người dùng Excel thành thạo.

Về mặt tiêu cực, hàm VLOOKUP bị giới hạn và có các giá trị mặc định nguy hiểm. Không giống như INDEX và MATCH (hoặc XLOOKUP), hàm VLOOKUP cần một bảng hoàn tất với những giá trị tra cứu trong cột đầu tiên. Điều này làm cho việc sử dụng hàm VLOOKUP với nhiều chỉ tiêu trở nên khó khăn. Ngoài ra, hành vi đối sánh mặc định của VLOOKUP khiến bạn đơn giản thu được kết quả không chính xác. Nhưng đừng quá lo lắng! Chìa khóa để sử dụng thành công hàm VLOOKUP là nắm rõ các kiến thức cơ bản.

VLOOKUP có 4 đối số: lookup_value, table_array, column_index_num và range_lookup. Lookup_value là giá trị cần tìm và table_array là độ rộng dữ liệu dọc cần tìm bên trong. Cột đầu tiên của table_array phải chứa các giá trị tra cứu để tìm kiếm. Đối số column_index_num là số cột của giá trị cần truy xuất, trong đấy cột đầu tiên của table_array là cột 1.

Cuối cùng, range_lookup điều khiển hành vi tìm giá trị khớp. Nếu range_lookup là TRUE, thì hàm VLOOKUP sẽ thi hành đối sánh gần đúng. Nếu range_lookup là FALSE, thì hàm VLOOKUP sẽ thực hiện đối sánh chính xác. Quan trọng, range_lookup là tùy chọn và đã được đặt mặc định là TRUE, do đó, VLOOKUP sẽ thực hành so khớp gần đúng theo mặc định.

Công thức của hàm VLOOKUP trong Excel

Hàm VLOOKUP trong Excel có công thức như sau:

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

Trong đó:

• VLOOKUP: Là tên hàm
• Các tham số in đậm sẽ phải có.
• lookup_value: Giá trị dùng làm dò tìm
• table_array: Bảng chứa giá trị cần dò tìm, để ở dạng giá trị tuyệt đối với dấu $ đằng trước, ví dụ: $A$3:$E$40.
• col_index_num: Thứ tự của cột chứa giá trị dò tìm trên table_array. Ví dụ trong bảng $A$3:$E$40, cột B chứa giá trị cần dò tìm thì col_index_num tại đây sẽ là 2; bảng $C$3:$F$40, cột E chứa giá trị dò tìm, thì col_index_num tại đây là 3.
• range_lookup: Là độ rộng tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối). Tham số này sẽ không sẽ phải luôn có trong công thức.

Dò tìm kha khá chỉ cũng đều có thể áp dụng khi chi phí trị cần dò tìm trong table_array đã được sắp xếp theo thứ tự (tăng dần hoặc giảm dần hay theo bảng chữ cái). Với những bảng như vậy bạn cũng có thể có thể dùng dò tìm tương đối, khi đó nó tựa như như dùng hàm IF vô biên vậy. Đối với những giá trị cần dò tìm không thể hoặc chưa được bố trí hãy dùng dò tìm tuyệt đối để tìm chính xác giá trị.

Video chỉ dẫn dùng hàm VLOOKUP

VD1: Hàm VLOOKUP để đánh giá xếp loại của học sinh, nhân viên

Giả sử, bạn có bảng điểm học sinh như sau:

Và bảng quy định xếp loại như sau:

Quy định xếp loại
0	Yếu
5.5	Trung bình
7	Khá
8.5	Giỏi

Giờ ta sẽ sử dụng hàm VLOOKUP để nhập xếp loại cho các học sinh. Bạn để ý thấy rằng bảng quy định xếp loại đã được bố trí theo thứ tự từ thấp đến cao (từ yếu đến giỏi) nên trong tình huống này ta cũng đều có thể dùng dò tìm tương đối.

Trong Excel 2 bảng này được trình bày như sau:

Ở đây, giá trị cần dò tìm nằm ngay cột C, bắt đầu từ dòng thứ 6. Phạm vi kiếm tìm là $A$18:$B$21, thứ tự cột chứa giá trị dò tìm là 2.

Tại ô D6, bạn nhập vào công thức: =VLOOKUP($C6,$A$18:$B$21,2,1). Đây là công thức dò tìm tương đối, bạn cũng có thể thực hiện dò tìm tuyệt đối nếu muốn (hoặc do bảng xếp loại không được bố trí theo thứ tự) bằng phương pháp thêm 0 vào công thức như thế này: =VLOOKUP($C6,$A$18:$B$21,2,0). Nhấn Enter.

Nhấp chuột vào ô D6, xuất hiện ô vuông nhỏ ở góc dưới bên phải, bạn click chuột vào đây và kéo dọc xuống hết bảng để sao chép công thức xếp loại cho các học sinh còn lại.

Khi đó, ta có kết quả dùng hàm VLOOKUP để xếp loại học lực học sinh như sau:

Bạn có thắc mắc tại sao phải sử dụng $ trước C6 không? $ sẽ giúp cố định cột C, chỉ thay đổi các hàng khi bạn kéo công thức xuống toàn bảng. Còn $A$18:$B$21 để giúp cố định bảng quy định xếp loại, khiến nó khỏi bị thay đổi khi bạn kéo công thức.

VD2: Hàm VLOOKUP dò tìm tuyệt đối để lấy dữ liệu

Giả sử, bạn có 1 bảng dữ liệu nhân viên, lưu trữ mã nhân viên, họ tên, chức vụ. Một bảng khác lưu giữ mã nhân viên, quê quán, trình độ học vấn. Giờ bạn mong muốn điền tin tức quê quán, trình độ học vấn cho mỗi nhân viên thì nên làm như ra sao?

Giả sử bạn có bảng nhân viên và quê quán nhân viên như sau:

Giờ bạn mong muốn điền tin tức quê quán cho nhân viên. Tại ô D4, bạn nhập vào công thức dò tìm tuyệt đối như sau: =Vlookup($A4,$A$16:$C$25,2,0)

Rồi nhấn Enter. Nhấp vào ô vuông nhỏ xuất hiện dưới góc ô D4 và kéo xuống toàn bảng để sao chép công thức cho những chuyên viên khác.

Để điền thông tin trình độ cho những nhân viên, tại ô E4 bạn nhập vào công thức dò tìm tuyệt đối: =VLOOKUP($A4,$A$16:$C$25,3,0)

Bạn tiếp tục nhấn Enter và kéo xuống để sao chép công thức cho các chuyên viên còn lại, ta sẽ được kết quả như sau:

VD3: Dùng VLOOKUP để trích xuất dữ liệu

Tiếp tục với bộ dữ liệu của tỉ dụ 2, giờ ta sẽ đi kiếm quê quán của 3 chuyên viên là Nguyễn Hoàng Anh, Trần Vân Anh và Nguyễn Quang Vinh. Mình đã trích xuất ra thành một bảng mới F15:G18.

Việc dò tìm này mình sẽ thực hiện trên bảng A3:E13, sau khi đã được điền quê quán và trình độ. Lúc này, bạn nhập công thức dò tìm tuyệt đối sau vào ô G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > nhấn Enter.

Sao chép công thức cho 2 chuyên viên còn lại ta được kết quả như sau:

Lưu ý trong thí dụ này, giá trị dò tìm nằm ngay cột B, vì thế vùng dò tìm ta tính từ cột B mà không tính từ cột A.

VD4: Dùng hàm VLOOKUP trên 2 sheet Excel không giống nhau

Quay lại với bộ dữ liệu ở ví dụ 2 sau khi chuyên viên được điền xong trình độ và quê quán, ta đặt tên cho sheet là QTM.

Ở một sheet khác của bảng tính, đặt tên là QTM1, bạn cần lấy thông tin về trình độ và chức vụ của nhân viên với thứ tự bố trí của nhân viên đã thay đổi. Đây mới là khi bạn thấy được sức mạnh thực thụ của hàm VLOOKUP.

Để dò tìm dữ liệu về “Trình độ” của nhân viên, bạn nhập công thức: =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) vào ô C4 của sheet QTM1.

Trong đó:

• B4 là cột chứa giá trị dùng làm dò tìm.
• QTM! là tên sheet chứa bảng có mức giá trị cần dò tìm, sau tên sheet bạn thêm dấu !
• $B$3:$E$13 là bảng chứa giá trị dò tìm và sheet chứa bảng (QTM).
• 4 là số thứ tự của cột “Trình độ”, tính từ cột “Họ và tên” trên sheet QTM.
• 0 là dò tìm tuyệt đối.

Nhấn Enter, rồi sao chép công thức cho toàn bộ nhân viên còn lại trong bảng, ta được kết quả như sau:

Để dò tìm dữ liệu “Chức vụ” của nhân viên, tại ô D4 của sheet QTM1, bạn nhập vào công thức: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0), nhấn Enter.

Sao chép công thức cho các nhân viên còn lại, ta được như sau:

Sử dụng hàm VLOOKUP để làm gì?

Trước tiên chúng ta cần tìm lời đáp chính xác cho câu hỏi “chúng ta sử dụng hàm VLOOKUP để làm gì?”.

Hàm VLOOKUP được dùng để hỗ trợ tra cứu tin tức trong 1 trường dữ liệu hoặc bản kê dựa vào những mã định danh có sẵn.

Ví dụ, chèn hàm VLOOKUP đi cùng mã sản phẩm vào một bảng tính khác, nó sẽ hiển thị thông tin của mặt hàng tương ứng với mã đó. Những tin tức đó cũng có thể có thể là mô tả, giá thành, số lượng tồn kho, tùy theo nội dung công thức mà bạn viết.

Lượng thông tin cần tìm càng nhỏ thì khi viết hàm VLOOKUP sẽ càng khó khăn hơn. Thông thường bạn sẽ sử dụng hàm này vào một bảng tính tái sử dụng như mẫu. Mỗi lần nhập mã sản phẩm thích hợp, hệ thống sẽ truy xuất mọi thứ những tin tức cần thiết về mặt hàng tương ứng.

Những điều cần nhớ về hàm VLOOKUP

Dưới này là bản kê những điều quan trọng cần nhớ về hàm VLOOKUP trong Excel:

• Khi range_lookup bị bỏ qua, hàm VLOOKUP sẽ cấp phép đối sánh không chính xác, nhưng vẫn sẽ sử dụng đối sánh chính xác nếu có.
• Hạn chế thật to của hàm là nó luôn lấy giá trị bên phải. Hàm sẽ lấy dữ liệu từ các cột bên phải cột đầu tiên trong bảng.
• Nếu cột tra cứu chứa các giá trị trùng lặp, hàm VLOOKUP sẽ chỉ khớp giá trị đầu tiên.
• Hàm không phân biệt chữ hoa chữ thường.
• Giả sử có một công thức VLOOKUP hiện có trong một trang tính. Trong trường hợp đó, các công thức cũng đều có thể bị hỏng nếu bạn chèn một cột vào bảng, chính vì các giá trị chỉ mục cột được mã hóa cứng, không tự động thay đổi khi các cột được chèn hoặc xóa.
• VLOOKUP cho phép sử dụng các ký tự đại diện, ví dụ, dấu hoa thị (*) hoặc dấu chấm hỏi (?).
• Giả sử trong bảng bạn đang hoạt động với hàm chứa các số được nhập dưới dạng văn bản. Nếu chỉ lấy số dưới dạng văn bản từ một cột trong bảng, thì điều này không thành vấn đề. Nhưng nếu cột trước mắt của bảng chứa các số được nhập dưới dạng văn bản, lỗi #N/A! sẽ hiển thị nếu giá trị tra cứu cũng không ở dạng văn bản.
• Lỗi #N/A! diễn ra nếu hàm VLOOKUP không tìm thấy kết quả thích hợp với giá trị lookup_value đã cung cấp.
• Lỗi #REF! diễn ra nếu:
  
  • Đối số col_index_num lớn hơn số cột trong table_array được cung cấp
  • Công thức đã cố gắng tham chiếu đến các ô không tồn tại.
• Lỗi #VALUE! diễn ra nếu:
  
  • Đối số col_index_num nhỏ hơn 1 hoặc chưa được thừa nhận là giá trị số
  • Đối số range_lookup không được nhận dạng là một trong các giá trị logic TRUE hoặc FALSE.

Xem thêm:

• Cách sử dụng hàm IF trong Excel
• Cách dùng hàm AVERAGEIF trên Excel

hàm Vlookup,hàm Excel,Vlookup,sử dụng hàm Vlookup,hàm tìm kiếm trong Excel,cấu trúc hàm Vlookup,cách sử dụng Vlookup,vlookup là gì,dùng hàm vlookup giữa 2 file,vlookup excel

Nội dung Hàm VLOOKUP cách sử dụng và ví dụ cụ thể được tổng hợp sưu tầm biên tập bởi: Tin Học Trường Tín. Mọi ý kiến vui lòng gửi Liên Hệ cho truongtin.top để điều chỉnh. truongtin.top tks.