hoctin.vn

Cách sử dụng hàm vlookup trong excel và ví dụ cụ thể

Cách Sử Dụng Hàm VLOOKUP Trong Excel và Ví Dụ Cụ Thể

by

Hàm VLOOKUP là một trong những hàm phổ biến nhất trong excel nhưng nhiều người dùng còn thấy khó hiểu về nó. Trong bài viết này, hoctin.vn sẽ giới thiệu với các bạn hàm VLOOKUP là gì, các cách sử dụng hàm tìm kiếm VLOOKUP trong excel, cách phân biệt đơn giản khi nào dùng hàm VLOOKUP khi nào dùng hàm hlookup cùng các ví dụ minh họa cụ thể về hàm VLOOKUP.

Hàm VLOOKUP là hàm dò tìm, tìm kiếm giá trị trong khu vực các cột (tạm gọi là khu vực dữ liệu dò tìm) và trả về kết quả là giá trị của một ô trong một cột do người dùng lựa chọn từ khu vực dữ liệu dò tìm, khi tiêu thức tìm kiếm khớp với một giá trị trong cột đầu tiên của khu vực dữ liệu dò tìm.

Chữ “V” trong VLOOKUP là viết tắt của vertical nghĩa là “theo chiều dọc”, có nghĩa là bạn sử dụng VLOOKUP để tra cứu các giá trị trong bảng được sắp xếp theo chiều dọc, cần phân biệt với hàm HLOOKUP là “Horizontal”- tìm kiếm theo chiều ngang, theo hàng.

Mục Lục Bài Viết

1, Cú Pháp Hàm VLOOKUP Trong Excel

Lệnh VLOOKUP có cấu trúc như sau:

=VLOOKUP(Lookup_value, Table_array, Col_index_ num, [Range_lookup])

Trong đó:

  • Lookup_value: giá trị bạn muốn tìm kiếm, tra cứu.
    Nếu cần copy công thức vlookup cho nhiều cột mà không cần gõ đi gõ lại công thức, cần ấn F4 ba lần để cố định cột trong excel (A1 > $A1) nhằm copy công thức tự động, khi đó bạn chỉ cần sửa col_index_num.
  • Table_array: nơi bạn muốn tìm Lookup_value, tạm gọi là khu vực dữ liệu dò tìm.
    Giá trị tra cứu phải luôn nằm ở cột đầu tiên bên trái để hàm VLOOKUP có thể ra kết quả mà không phải là lỗi N/A.
    Cần ấn F4 một lần để cố định khu vực dữ liệu dò tìm trong excel nhằm copy công thức tự động (A1:E10 > $A$1:$E$10)
  • Col_index_ num: số thứ tự của cột trong dải ô chứa giá trị cần trả về trong kết quả của hàm VLOOKUP.
    Khi sử dụng hàm VLOOKUP, hãy tưởng tượng rằng mọi cột trong bảng được đánh số, bắt đầu từ bên trái: Ví dụ nếu phạm vi khu vực dữ liệu dò tìm là A2:C10 và giá trị tìm kiếm khớp cột A thì trả về kết quả là ô tương ứng tại cột C, thì thứ tự đếm cột A là cột đầu tiên, B là thứ hai, C là thứ ba nên Col_index_num bằng 3.
  • [Range_lookup]: phạm vi tìm kiếm
    Nếu Range_lookup = 1 hoặc TRUE: dò tìm kết quả phù hợp gần đúng
    Nếu Range_lookup =  0 hoặc FALSE: dò tìm kết quả chính xác, nếu không tìm thấy sẽ hiện lỗi N/A.
  • Các tham số in đậm là bắt buộc, [Range_lookup] nếu không được nhập sẽ được mặc định là 1 hoặc TRUE là kết quả tương đối.

2, Ví Dụ Minh Họa Đơn Giản Cách Dùng Hàm VLOOKUP Trong Excel

2.1, Ví Dụ Công Thức VLOOKUP Kết Quả Khớp Chính Xác (0/FALSE)

Ví dụ cách tìm tên trong excel  thông qua hàm VLOOKUP dựa trên ID của nhân viên. 

 

Như bảng dữ liệu dưới đây, hàm vlookup chỉ có thể hỗ trợ bạn tìm Họ tên và Ngày bắt đầu làm việc của nhân viên thông qua ID của họ, bạn luôn phải sắp xếp dữ liệu sao cho giá trị mà bạn muốn tra cứu (ID) nằm ở bên trái giá trị trả về (Họ tên, ngày bắt đầu làm việc).

 

Hàm VLOOKUP có hạn chế đáng kể khi sử dụng là nó không thể dò tìm phía bên trái cột tra cứu. Do đó, cần di chuyển dữ liệu Bộ phận và Vị trí sang cột F, G để có thể tra cứu thông qua ID hoặc dùng hàm tìm kiếm có điều kiện trong excel qua việc sử dụng kết hợp hàm Index và Match.

Cách sử dụng hàm Vlookup trong excel và ví dụ cụ thể

Trong ví dụ trên:

  • Tại ô D16, để tìm kiếm Họ tên của ID tại ô D15, gõ công thức =VLOOKUP(D15,C2:E10,2,0)
  • Tại ô D17, để tìm kiếm Ngày bắt đầu làm việc của ID tại ô D15, gõ công thức =VLOOKUP(D15,C2:E10,3,0)

    Trong đó:
  • VLOOKUP: là hàm dùng để tìm kiếm các thông tin của nhân viên từ bảng dữ liệu theo ID của họ
  • D15: Cơ sở dò tìm dựa trên ID của nhân viên V00038
  • C2:E10: Bảng màu xanh da trời nhạt thể hiện giới hạn dò tìm
  • 2, 3: Thứ tự cột giá trị cần lấy, lần lượt là cột Họ tên và Ngày bắt đầu làm việc
  • 0: Khi đối số range_lookup là 0 (FALSE), nếu hàm vlookup không thể tìm thấy kết quả khớp chính xác giá trị V00038 trong cột đầu tiên của khu vực dữ liệu dò tìm (cột ID), nó sẽ trả về một lỗi #N/A (Not available- Không có số liệu)

2.2, Ví Dụ Công Thức VLOOKUP Kết Quả Khớp Tương Đối (1/TRUE)

Ví dụ cách dùng hàm VLOOKUP tìm kiếm xếp loại học sinh, sinh viên và nhập dữ liệu xếp loại này vào cột C khi đã có các thông tin sau:

  • Điểm tổng kết ở cột B;

  • Hướng dẫn quy định xếp loại của trường ở cột E và F

Ví dụ công thức VLOOKUP kết quả khớp tương đối (1/TRUE)

Trong ví dụ trên:

  • Tại ô C4, để tìm kiếm Xếp loại của học sinh có Điểm tổng kết tại ô B4, gõ công thức
    =VLOOKUP(B4,$E$2:$F$7,2,1) hoặc =VLOOKUP(B4,$E$2:$F$7,2,TRUE)
  • Copy công thức này cho toàn bộ cột C, ta sẽ tìm được xếp loại tương ứng với điểm của từng học sinh.
  • Hàm VLOOKUP tương đối được mặc định nếu không nhập [Range_lookup]  nên công thức tại ô C4 có thể nhập là =VLOOKUP(B4,$E$2:$F$7,2)

    Trong đó:
  • VLOOKUP: là hàm dùng để tìm kiếm Xếp loại của học sinh dựa trên Điểm tổng kết của học sinh đó.
  • B4: Cơ sở dò tìm là Điểm tổng kết của học sinh (7.5)
  • $E$2:$F$7: Bảng màu xanh da trời nhạt thể hiện giới hạn dò tìm đã được sắp xếp theo thứ tự từ thấp đến cao (từ học lại đến xuất sắc).
    Bảng này được cố định trong excel để khi thực hiện tìm kiếm xếp loại của các học sinh khác: ta copy công thức từ ô C4 đến các ô khác trong cột C thì giới hạn dò tìm không thay đổi.
  • 2: Thứ tự cột giá trị cần lấy là Quy định xếp loại
  • 1: Đối số range_lookup là 1 (TRUE), trong trường hợp này ta có thể dùng dò tìm tương đối để dễ dàng dò tìm theo khoảng và thống kê trong excel.

    Lưu ý:     Hàm VLOOKUP dò tìm tương đối (1/TRUE) tương tự như dùng hàm IF vô hạn. Hàm VLOOKUP này chỉ có thể áp dụng khi giá trị cần dò tìm trong khu vực dữ liệu dò tìm (table_array) đã được sắp xếp theo thứ tự (tăng dần hoặc theo bảng chữ cái). 
    Nếu không thể hoặc chưa sắp xếp được giá trị cần dò tìm thì hàm VLOOKUP dò tìm tương đối không hoạt động chính xác.

    Tìm hiểu thêm về: cách sử dụng hàm if trong excel

2.3, Copy và Sử Dụng Nhanh Hàm VLOOKUP Bằng Cách Cố Định Ô Trong Excel

Tiếp tục với ví dụ 2.2 ở trên, trường hợp phải quy đổi điểm hệ 10 của sinh viên sang hệ 4 và hệ chữ ở trường đại học. Các thông tin đã có:

  • Điểm tổng kết ở cột B;

  • Hướng dẫn quy định xếp loại của trường ở cột G:J

Như vậy, cần nhập hàm VLOOKUP tìm kiếm theo Điểm tổng kết môn để kết xuất dữ liệu lần lượt là Điểm hệ 4 quy đổi, Điểm chữ quy đổi, Xếp loại ở cột C > E. Hoctin.vn sẽ hướng dẫn các bạn cách copy nhanh công thức VLOOKUP mà không cần phải gõ lại trong từng ô từ cột C đến cột E:

Copy và sử dụng nhanh hàm VLOOKUP bằng cách cố định ô trong excel
  • Tại ô C5, nhập công thức: =VLOOKUP($B5,$G$5:$J$13,3,TRUE), trong đó:
  • VLOOKUP: là hàm dùng để tìm kiếm Điểm hệ 4 quy đổi của học sinh dựa trên Điểm tổng kết của học sinh đó.
  • B5: Cơ sở dò tìm là Điểm tổng kết của học sinh (9.1). Sau khi nhập B5, ấn F4 ba lần để cố định cột trong excel: B5 sẽ hiển thị thành $B5
  • $E$2:$F$7: Bảng màu xanh da trời nhạt thể hiện giới hạn dò tìm đã được sắp xếp theo thứ tự từ thấp đến cao (từ học lại đến xuất sắc).
    Bảng này được cố định trong excel bằng cách ấn F4 một lần.
  • 3: Thứ tự cột giá trị cần lấy là Quy định xếp loại
  • TRUE: Đối số range_lookup là 1 (TRUE), tương tự ví dụ 2.2
  • Tại các ô còn lại của cột C, copy công thức ô C5 và dán (Ctrl C Ctrl V)
  • Tại ô D5, copy công thức từ ô C5 sang, sửa Col_index_ num từ 3 thành 2 do Thang điểm chữ là cột thứ 2 trong khu vực dữ liệu dò tìm
    =VLOOKUP($B5,$G$5:$J$13,2,TRUE)
    Copy công thức từ D5 cho các ô cột D
  • Lặp lại tương tự với ô E5 và cột E: =VLOOKUP($B5,$G$5:$J$13,4,TRUE)

3, Lưu Ý Khi Sử Dụng Hàm VLOOKUP

3.1, Dùng Hàm VLOOKUP Tìm Dữ Liệu Trong Nhiều Sheet, Trích Xuất Dữ Liệu Từ Sheet Này Sang Sheet Khác

Ứng dụng lấy dữ liệu từ bảng này sang bảng khác trong excel: Ví dụ cách dùng hàm VLOOKUP giữa 2 sheet để tính đơn giá trong excel: Để tra cứu đơn giá của sản phẩm với mã HT003 trên sheet Sale tháng 6, ta sẽ bắt đầu viết công thức VLOOKUP trong ô C10, sheet Sale tháng 6 như khi dò tìm dữ liệu trong 1 sheet giống các ví dụ ở trên.

 

Khi chọn vùng dữ liệu dò tìm, bấm sang sheet Đơn giá, sau đó chọn vùng dữ liệu như A3:B22 trong sheet Đơn giá, Excel sẽ hiển thị công thức VLOOKUP tự động đúng như trên hình minh hoạ: =VLOOKUP(A10,’Đơn giá’!$A$3:B$22,2,0)

Dùng hàm VLOOKUP tìm dữ liệu trong nhiều sheet, trích xuất dữ liệu từ sheet này sang sheet khác

3.2, Sử Dụng Hàm VLOOKUP Tra Cứu Ở Một File Excel Khác

Vẫn là ví dụ tính đơn giá trong excel như trên: trường hợp sheet Đơn giá không nằm cùng 1 file với Bảng kê bán hàng tháng 06/2020 (sheet Sale tháng 6) mà nằm trong 1 file excel riêng biệt: đơn giá T6.xlsx, hoctin.vn sẽ hướng dẫn bạn cách dùng hàm VLOOKUP giữa 2 file excel khác nhau:

 

Để tra cứu đơn giá của sản phẩm với mã HT002 trên sheet Sale tháng 6, ta cần mở cả 2 file excel, gồm file cần viết công thức và file nguồn dữ liệu tra cứu. Ta sẽ bắt đầu viết công thức VLOOKUP trong ô C9, sheet Sale tháng 6 như bình thường.

 

 

Khi chọn vùng dữ liệu dò tìm, khi gõ xong Lookup_value là B9, gõ dấu phẩy “,” sau đó chuyển sang file đơn giá T6.xlsx, sheet DG, chọn vùng dữ liệu như B3:C22 trong sheet này, Excel sẽ hiển thị công thức VLOOKUP tự động đúng như trên hình minh hoạ: =VLOOKUP(B9,'[đơn giá T6.xlsx]DG’!B$3:C$22,2,0)

Sử dụng hàm VLOOKUP tra cứu ở 1 file excel khác

Trường hợp file đơn giá T6.xlsx đóng thì công thức VLOOKUP của chúng ta vẫn sẽ hoạt động, nhưng tự động được “viết lại” với đường dẫn tới file Excel, bao gồm thư mục chứa file dữ liệu như sau:

=VLOOKUP(B9,’C:\Users\LENOVO\Desktop\[đơn giá T6.xlsx]DG’!B$3:C$22,2,0)

Sử dụng hàm VLOOKUP tra cứu ở 1 file excel khác

3.3, Viết Hàm VLOOKUP Sử Dụng Ký Tự Thay Thế

Hàm VLOOKUP tra cứu không phân biệt chữ hoa, chữ thường. Giống như các hàm Excel khác, hàm VLOOKUP hỗ trợ sử dụng ký tự sao “*” để thay thế một hoặc một số ký tự và ký tự “?” để thay thế cho một và chỉ một ký tự trong tham số lookup_value. Ví dụ:

Hàm VLOOKUP tra cứu không phân biệt chữ hoa, chữ thường, hỗ trợ sử dụng ký tự sao “*” để thay thế một hoặc một số ký tự và ký tự “?” để thay thế cho một và chỉ một ký tự trong tham số lookup_value

3.4, Các Lỗi Khi Dùng Hàm VLOOKUP

Hàm VLOOKUP không chạy, trả về giá trị sai thường gây khó khăn cho người sử dụng. Một số lỗi xảy ra khi dùng hàm VLOOKUP:

– Lỗi #NAME: khi gõ sai cú pháp hàm.

– Lỗi #NA: không tìm được giá trị cần dò tìm. Hoctin.vn đã có bài viết cụ thể về cách sửa lỗi #N/A trong Excel hướng dẫn chi tiết về các lỗi N/A thường gặp ở các hàm, đặc biệt là hàm VLOOKUP.

– Lỗi #REF: cột dò tìm không có trong bảng chứa giá trị dò tìm, col_index_num lớn hơn số cột trong table_array.

– Lỗi #VALUE!: col_index_num của bạn nhỏ hơn 1.

Để hiểu sâu hơn về các lỗi khi sử dụng hàm VLOOKUP và ứng dụng hướng dẫn sử dụng hàm VLOOKUP trong các bài tập excel cơ bản: hàm VLOOKUP nhiều điều kiện, hàm VLOOKUP 2 điều kiện, cách kết hợp VLOOKUP và HLOOKUP, … các bạn đón đọc Các ví dụ về hàm tìm kiếm trong excel.

 

Hi vọng bài viết của hoctin.vn đã mang lại cho các bạn các thông tin hữu ích về hàm VLOOKUP trong excel. Ngoài ra, để ứng dụng hiệu quả Excel, bạn cần phải sử dụng tốt các hàm, các công cụ khác của Excel, hãy cùng đón đọc series bài viết ứng dụng các hàm Excel:

Hàm IF

Hàm IF kết hợp VLOOKUP

Cách sửa lỗi #N/A của hàm VLOOKUP

Hàm INDEX MATCH

Leave a Comment