Đánh giá lequocthai.com:
Thuật ngữ “Vertical” chỉ ra rằng hàm có thể tra cứu giá trị dọc trong một cột.
Bạn cũng có thể đọc bài trước của tôi: “Cách sắp xếp chữ cái trong Excel”, trong đó tôi đã dùng VLOOKUP để sắp xếp danh sách.
Ghi chú:
: Bài viết này dài hơn 2700 từ. Nếu không muốn đọc toàn bộ, bạn có thể dùng các liên kết nhảy phía dưới để tới phần mong muốn.
Định nghĩa VLOOKUP trong Excel
Theo Microsoft, VLOOKUP là một hàm “tìm một giá trị trong cột bên trái nhất của một bảng, sau đó trả về giá trị trong cùng một hàng ở cột mà bạn chỉ định. Mặc định, bảng phải được sắp xếp theo thứ tự tăng dần.”
Các ứng dụng của hàm Vertical Lookup
Hàm này thường được dùng cho các công việc sau:
- Tra cứu một hoặc nhiều giá trị từ một bảng dữ liệu.
- Thêm cột vào bảng dữ liệu dựa trên một bảng khác, dựa trên một (các) thuộc tính duy nhất.
Cú pháp của hàm VLOOKUP
Cú pháp như sau:
=VLOOKUP( lookup_value, table_array, column_index, range_lookup )- lookup_value: Giá trị cần tìm trong table_array. Có thể là một giá trị thực hoặc một ô tham chiếu.
- table_array: Phạm vi gồm ít nhất hai cột. Đối số này có thể là một tham chiếu phạm vi hoặc một tên vùng. Cột bên trái nhất của phạm vi này phải chứa lookup_value.
- column_index: Số thứ tự tương đối của cột mà hàm VLOOKUP sẽ trả về. Ví dụ, `1` sẽ trả về giá trị từ cột đầu tiên của table_array, `2` sẽ trả về giá trị từ cột thứ hai, v.v.
- range_lookup: Giá trị Boolean xác định bạn muốn VLOOKUP tìm khớp chính xác hay gần đúng. Nếu là `TRUE` (mặc định) thì cho phép khớp gần đúng; nếu là `FALSE` thì chỉ chấp nhận khớp chính xác.
Một số điểm quan trọng về hàm Vertical Lookup
- Hàm VLOOKUP không phân biệt chữ hoa/chữ thường.
- Nếu không tìm thấy lookup_value trong table_array, hàm sẽ trả về lỗi `#N/A`.
- Nếu column_index < 1, hàm sẽ trả về lỗi `#VALUE!`.
- Nếu column_index > số cột của table_array, hàm sẽ trả về lỗi `#REF!`.
- Giá trị mặc định của range_lookup là `TRUE`, vì vậy trong nhiều trường hợp bạn có thể bỏ qua đối số này nếu muốn khớp gần đúng.
- Vertical Lookup cho phép sử dụng ký tự đại diện (wildcards) trong lookup_value; chúng tôi sẽ thảo luận chi tiết sau.
Cách sử dụng Excel VLOOKUP?
Trước khi đi vào cách sử dụng, chúng ta cần hiểu mục tiêu của hàm. Hãy xem qua một ví dụ thực tế.
Giả sử chúng ta có bảng như sau:
Mục tiêu:
Tìm mức lương của một nhân viên cụ thể (ví dụ: Benjamin Mutricy) dựa trên tên của họ.
Giải pháp:
Áp dụng VLOOKUP để lấy kết quả.
- lookup_value: Giá trị dùng để tra cứu. Ở đây là ô B4 chứa “Benjamin Mutricy”.
- table_array: Phạm vi bảng chứa dữ liệu cần lấy. Lưu ý table_array phải luôn có lookup_value ở cột trái nhất.
- col_index_num: Chỉ số vị trí của cột mà VLOOKUP sẽ trả về.
- range_lookup: Xác định khớp chính xác hay gần đúng. `FALSE` nghĩa là khớp chính xác.
Vì vậy công thức sẽ là:
=VLOOKUP(B4,B7:D14,3,FALSE)5 ví dụ dành cho người mới bắt đầu
Ví dụ 1
Danh sách học sinh và điểm số. Tìm điểm của học sinh có tên “Vincent”.
Công thức:
=VLOOKUP(E4,A3:C16,3,FALSE)Kết quả: 75.
Giải thích: `lookup_value` = E4 (Vincent), `table_array` = A3:C16, `column_index` = 3, `range_lookup` = FALSE.
Ví dụ 2
Tìm họ của học sinh “Emmanuel”.
Công thức:
=VLOOKUP(E4,A3:C16,2,FALSE)Kết quả: “Schauly”.
Ví dụ 3
Bảng “Element Table” gồm các cột Khối lượng Nguyên tử, Độ đặc và Tên nguyên tố. Tìm Độ đặc của nguyên tố có khối lượng Nguyên tử bằng 15 hoặc gần hơn.
Công thức:
=VLOOKUP(15,A3:B14,2)Kết quả: 1.25.
Giải thích: `range_lookup` bị bỏ qua nên mặc định là TRUE, do đó VLOOKUP sẽ tìm giá trị gần nhất thấp hơn hoặc bằng 15.
Ví dụ 4
Hai bảng: Bảng 1 chứa tên nguyên tố, Bảng 2 chứa điểm nóng chảy. Lấy điểm nóng chảy cho mỗi nguyên tố.
Công thức:
=VLOOKUP(C3,$F$3:$G$14,2,FALSE)Sau khi nhập công thức cho ô đầu tiên, kéo công thức xuống để áp dụng cho các ô còn lại.
Ví dụ 5
Sử dụng ký tự đại diện để tìm điểm của học sinh có tên bắt đầu bằng “A”.
Công thức:
=VLOOKUP("A*",A3:C16,3,FALSE)Kết quả: 66.
Ký tự đại diện:
- `?` – đại diện cho một ký tự duy nhất.
- `*` – đại diện cho bất kỳ số ký tự nào.
Lưu ý: Đặt dấu ngã (`~`) trước ký tự đại diện nếu bạn muốn nó được xử lý như một chuỗi thông thường, không phải là ký tự đại diện.
Một số ví dụ thực tế
Ví dụ 6 – Kiểm tra sự tồn tại
Có danh sách “Child List” và “Master List”. Dùng VLOOKUP để xác định mỗi mục trong “Child List” có tồn tại trong “Master List” hay không.
Công thức:
=IF(ISNA(VLOOKUP(B2,$G$2:$G$17,1,0)),"Not Exists", "Exists")Giải thích: Nếu VLOOKUP trả về lỗi #N/A thì mục không tồn tại, ngược lại tồn tại.
Ví dụ 7 – Chuyển điểm số thành xếp hạng
Bảng điểm và bảng xếp hạng. Dùng VLOOKUP để gán xếp hạng dựa trên điểm.
Công thức:
=VLOOKUP(C3,$F$4:$G$9,2,TRUE)Kéo công thức xuống các ô còn lại.
Ví dụ 8 – Tra cứu trên một sheet hoặc workbook khác
Bảng “Student Table” nằm ở Sheet1, bảng “Grade and Score” ở Sheet2.
Công thức:
=VLOOKUP(C3,Sheet2!$B$3:$C$8,2,TRUE)Mẹo: Khi viết công thức, bạn có thể chuyển tới sheet chứa bảng và chọn phạm vi; Excel sẽ tự chèn tham chiếu đầy đủ.
Sử dụng VLOOKUP trong VBA
Bạn cũng có thể gọi hàm VLOOKUP từ macro VBA. Ví dụ:
Sub Vertical_lookup_test()
Dim Result As Variant
Dim myVal As String 'Có thể là Integer, Long, Double, …
Dim Rng As Range
Dim Clm As Integer
Set Rng = ActiveSheet.Range("A:E") 'Thiết lập phạm vi
myVal = "Florian" 'Giá trị cần tìm
Clm = 3 'Cột muốn lấy
Result = Application.VLookup(myVal, Rng, Clm, False)
If IsError(Result) Then
Result = "Not found!"
End If
MsgBox Result
End Sub
Bạn cần chỉnh lại: 1) Phạm vi `Set Rng = …`; 2) Giá trị `myVal = …`; 3) Cột `Clm = …`.
Đọc thêm
- VLOOKUP trong VBA – Kèm ví dụ
Trả về nhiều cột từ một VLOOKUP
Mặc định VLOOKUP chỉ trả về một cột. Để lấy nhiều cột, hãy dùng VLOOKUP như một công thức mảng.
- Chọn các ô tương đương số cột cần lấy.
- Nhập công thức:
=VLOOKUP("Florian",A:D,{1,2,3,4},FALSE)- Nhấn Ctrl+Shift+Enter để biến công thức thành mảng.
VLOOKUP ngược (Negative VLOOKUP)
Thông thường `lookup_value` phải nằm ở cột trái nhất. Nếu muốn tra cứu từ một cột không phải là cột trái, bạn có thể tạo một hàm do người dùng định nghĩa (UDF).
Function NEGATIVE_VLOOKUP(lookup_value, table_array As Range, col_index_num As Integer, CloseMatch As Boolean)
Dim RowNbr As Long
RowNbr = Application.WorksheetFunction.Match(lookup_value, table_array.Resize(, 1), CloseMatch)
NEGATIVE_VLOOKUP = table_array(RowNbr, 1).Offset(0, col_index_num)
End Function
Sau đó sử dụng:
=NEGATIVE_VLOOKUP("Petit",B:B,-1,FALSE)hoặc
=NEGATIVE_VLOOKUP(98,D:D,-3,FALSE)Giải thích: `col_index_num` âm cho phép lấy giá trị ở phía bên trái `lookup_value`.
VLOOKUP với nhiều tiêu chí
Khi cần dựa trên hai trường (ví dụ: Họ và Tên) để tra cứu, tạo một cột trợ giúp bằng cách ghép hai trường lại:
=A3 & " " & B3Sau khi có cột duy nhất, áp dụng VLOOKUP bình thường:
=VLOOKUP("Christophe Pracht",C:D,2,FALSE)—
