H
Bài viết này mô tả cú pháp công thức và cách dùng hàm VLOOKUP trong Microsoft Excel.
Mô tả
Bạn có thể dùng hàm VLOOKUP để tìm cột đầu tiên của một phạm vi ô, sau đó trả về một giá trị từ bất kỳ ô nào trên cùng hàng của phạm vi. Ví dụ, giả sử bạn có một danh sách các nhân viên trong phạm vi A2:C10. Số ID của các nhân viên được lưu trữ trong cột đầu tiên của phạm vi, như thể hiện trong minh họa sau đây.
Một phạm vi ô nằm trên một trang tính
Nếu bạn biết số ID của nhân viên, bạn có thể dùng hàm VLOOKUP để trả về bộ phận hoặc tên của nhân viên đó. Để có được tên của nhân viên số 38, bạn có thể dùng công thức =VLOOKUP(38, A2:C10, 3, FALSE). Công thức này tìm giá trị 38 trong cột đầu tiên của phạm vi A2:C10, sau đó trả về giá trị được chứa trong cột thứ ba của phạm vi và trên cùng hàng làm giá trị tra cứu ("Axel Delgado").
Chữ V trong VLOOKUP đại diện cho vertical (theo chiều dọc). Dùng VLOOKUP thay cho HLOOKUP khi các giá trị so sánh của bạn được đặt trong một cột ở bên trái của dữ liệu mà bạn muốn tìm.
Cú pháp
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Cú pháp hàm VLOOKUP có các đối số dưới đây:
lookup_value Bắt buộc. Các giá trị để tìm kiếm trong cột đầu tiên của bảng hoặc phạm vi. Đối số lookup_value có thể được xem như là một giá trị hoặc một tham chiếu. Nếu giá trị bạn cung cấp cho đối số lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của đối số table_array, VLOOKUP trả về giá trị lỗi #N/A.
table_array Bắt buộc. Phạm vi ô có chứa dữ liệu. Bạn có thể dùng một tham chiếu cho một phạm vi (ví dụ, A2
8), hoặc một tên phạm vi. Các giá trị trong cột đầu tiên của table_array là các giá trị được tìm kiếm bởi lookup_value. Những giá trị này có thể là văn bản, số hoặc giá trị lô-gic. Văn bản chữ hoa và chữ thường tương đương nhau.
col_index_num Bắt buộc. Số cột trong đối số table_array mà giá trị khớp phải được trả về từ đó. Một đối số col_index_num của 1 trả về giá trị trong cột đầu tiên trong table_array; một col_index_num của 2 trả về giá trị trong cột thứ hai trong table_array, và cứ như vậy.
Nếu đối số col_index_num là:
Nhỏ hơn 1, VLOOKUP trả về giá trị lỗi #VALUE .
Lớn hơn số lượng cột trong table_array, VLOOKUP trả về giá trị lỗi #REF! .
range_lookup Tùy chọn. Một giá trị lô-gic xác định bạn có muốn VLOOKUP tìm thấy một kết quả khớp chính xác hay kết quả khớp tương đối:
Nếu range_lookup là TRUE hoặc bị bỏ qua, một kết quả khớp chính xác hoặc tương đối được trả về. Nếu một kết quả khớp chính xác không được tìm thấy thì giá trị lớn nhất kế tiếp nhỏ hơn lookup_value được trả về.
QUAN TRỌNG Nếu range_lookup là TRUE hoặc bị bỏ qua, các giá trị ở cột đầu tiên của table_array phải được đặt theo trật tự sắp xếp giảm dần; nếu không, VLOOKUP có thể không trả về giá trị đúng.
Để biết thêm thông tin, vui lòng xem Sắp xếp dữ liệu trong phạm vi hoặc bảng.
Nếu range_lookup là FALSE, các giá trị trong cột đầu tiên của table_array không cần được sắp xếp.
Nếu đối số range_lookup là FALSE, VLOOKUP sẽ chỉ tìm một kết quả khớp chính xác. Nếu có hai hoặc nhiều giá trị trong cột đầu tiên của table_array khớp với lookup_value, giá trị đầu tiên được tìm thấy sẽ được sử dụng. Nếu một kết quả khớp chính xác không được tìm thấy, một giá trị lỗi #N/A được trả về.
Chú thích
Khi tìm kiếm các giá trị văn bản trong cột đầu tiên của table_array, hãy đảm bảo rằng dữ liệu trong cột đầu tiên của table_array không chứa dấu cách trống ở đầu, dấu cách trống ở cuối, việc sử dụng không nhất quán dấu nháy kép thẳng ( ' hoặc " ) và dấu nháy kép cong ( ‘ hoặc “), hoặc các ký tự không in ra. Trong những trường hợp này, VLOOKUP có thể trả về một giá trị không chính xác hoặc không được mong đợi.
Để biết thêm thông tin, vui lòng xem Hàm CLEAN và hàm TRIM.
Khi tìm kiếm các giá trị ngày, hãy bảo đảm dữ liệu trong cột đầu tiên của table_array không được lưu trữ như là các giá trị văn bản. Trong trường hợp này, VLOOKUP có thể trả về một giá trị không đúng hoặc không được mong đợi.
Nếu range_lookup là FALSE và lookup_value là văn bản, bạn có thể sử dụng các ký tự đại diện — dấu chấm hỏi (?) và dấu sao (*) — trong lookup_value. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
Ví dụ
Sao chép dữ liệu của ví dụ trong bảng sau đây và dán vào ô A1 của một trang tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu.ban nha Nam Tu Liem
MẬT ĐỘ ĐỘ NHỚT NHIỆT ĐỘ
0,457 3,55 500
0,525 3,25 400
0,606 2,93 300
0,675 2,75 250
0,746 2,57 200
0,835 2,38 150
0,946 2,17 100
1,09 1,95 50
1,29 1,71 0
Công thức Mô tả Kết quả
=VLOOKUP(1,A2:C10,2) Dùng khớp gần đúng để tìm kiếm giá trị 1 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 1 trong cột A là 0,946 và sau đó trả về giá trị từ cột B trong cùng hàng. 2,17
=VLOOKUP(1,A2:C10,3,TRUE) Dùng khớp gần đúng để tìm kiếm giá trị 1 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 1 trong cột A là 0,946 và sau đó trả về giá trị từ cột C trong cùng hàng. 100
=VLOOKUP(0.7,A2:C10,3,FALSE) Dùng khớp chính xác để tìm giá trị 0,7 trong cột A. Vì không có sự khớp chính xác trong cột A, lỗi được trả về #N/A
=VLOOKUP(0.1,A2:C10,2,TRUE) Dùng khớp gần đúng để tìm giá trị 0,1 trong cột A. Vì 0,1 nhỏ hơn giá trị nhỏ nhất trong cột A, lỗi được trả về. #N/A
=VLOOKUP(2,A2:C10,2,TRUE) Dùng khớp gần đúng để tìm kiếm giá trị 2 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 2 trong cột A là 1,29 và sau đó trả về giá trị từ cột B trong cùng hàng. 1,71
Ví dụ 2
ID MỤC MỤC COST TĂNG GIÁ
ST-340 Ghế đẩy $145,67 30%
BI-567 Yếm dãi $3,56 40%
DI-328 Tả lót $21,45 35%
WI-989 Khăn tay $5,12 40%
AS-469 Máy hút gió $2,56 45%
Công thức Mô tả Kết quả
= VLOOKUP("DI-328", A26, 3, FALSE) * (1 + VLOOKUP("DI-328", A26, 4, FALSE)) Tính toán giá bán lẻ của tả lót bằng cách thêm tỷ lệ phần trăm tăng giá vào chi phí. $28,96
= (VLOOKUP("WI-989", A26, 3, FALSE) * (1 + VLOOKUP("WI-989", A26, 4, FALSE))) * (1 - 20%) Tính toán giá bán của khăn tay bằng cách trừ đi một phần chiết khấu đã xác định trong giá bán lẻ. $5,73
= IF(VLOOKUP(A2, A26, 3, FALSE) >= 20, "Giá tăng thêm là " & 100 * VLOOKUP(A2, A26, 4, FALSE) &"%", "Chi phí dưới $20,00") Nếu chi phí của một mặt hàng lớn hơn hoặc bằng $20,00, sẽ hiển thị chuỗi "Giá tăng thêm là nn%"; nếu không, sẽ hiển thị chuỗi "Chi phí dưới $20,00" Giá tăng thêm là 30%
= IF(VLOOKUP(A3, A26, 3, FALSE) >= 20, "Giá tăng thêm là: " & 100 * VLOOKUP(A3, A26, 4, FALSE) &"%", "Chi phí là $" & VLOOKUP(A3, A26, 3, FALSE)) Nếu chi phí của một mặt hàng lớn hơn hoặc bằng $20,00, sẽ hiển thị chuỗi Giá tăng thêm là nn%"; nếu không, sẽ hiển thị chuỗi Chi phí là $n.nn" Chi phí là $3,56
Ví dụ 3
ID HỌ TÊN CHỨC DANH NGÀY SINH
1 Davis Sara Đại diện Bán hàng 08/12/68
2 Fontana Olivier Phó chủ tịch Kinh doanh 19/02/1952
3 Leal Karina Đại diện Bán hàng 30/08/1963
4 Patten Michael Đại diện Bán hàng 19/09/1958
5 Burke Brian Quản lý Bán hàng 04/03/1955
6 Sousa Luis Đại diện Bán hàng 02/07/1963
Công thức Mô tả Kết quả
=INT(YEARFRAC(DATE(2004,6,30), VLOOKUP(5,A2:E7,5, FALSE), 1)) Đối với năm tài chính 2004, tìm tuổi của nhân viên có ID là 5. Dùng hàm YEARFRAC để trừ đi ngày sinh từ ngày kết thúc năm tài chính và hiển thị kết quả là một số nguyên bằng hàm INT. 49
=IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "Không tìm thấy nhân viên", VLOOKUP(5,A2:E7,2,FALSE)) Nếu có một nhân viên có ID bằng 5, sẽ hiển thị họ của nhân viên; nếu không, sẽ hiển thị thông báo "Không tìm thấy nhân viên".
Hàm ISNA trả về giá trị TRUE khi hàm VLOOKUP trả về giá trị lỗi #N/A. Burke
=IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "Không tìm thấy nhân viên", VLOOKUP(15,A3:E8,2,FALSE)) Nếu có một nhân viên có ID bằng 15, sẽ hiển thị họ của nhân viên; nếu không, sẽ hiển thị thông báo "Không tìm thấy nhân viên".
Hàm ISNA trả về một giá trị TRUE khi hàm VLOOKUP trả về giá trị lỗi #N/A. Không tìm thấy nhân viên
=VLOOKUP(4,A2:E7,3,FALSE) & " " & VLOOKUP(4,A2:E7,2,FALSE) & " là " & VLOOKUP(4,A2:E7,4,FALSE) Đối với nhân viên có ID bằng 4, ghép nối (kết hợp) các giá trị bao gồm ba ô trong một câu hoàn chỉnh. Michael Patten là một Đại diện Bán hàng.
Mô tả
Bạn có thể dùng hàm VLOOKUP để tìm cột đầu tiên của một phạm vi ô, sau đó trả về một giá trị từ bất kỳ ô nào trên cùng hàng của phạm vi. Ví dụ, giả sử bạn có một danh sách các nhân viên trong phạm vi A2:C10. Số ID của các nhân viên được lưu trữ trong cột đầu tiên của phạm vi, như thể hiện trong minh họa sau đây.
Một phạm vi ô nằm trên một trang tính
Nếu bạn biết số ID của nhân viên, bạn có thể dùng hàm VLOOKUP để trả về bộ phận hoặc tên của nhân viên đó. Để có được tên của nhân viên số 38, bạn có thể dùng công thức =VLOOKUP(38, A2:C10, 3, FALSE). Công thức này tìm giá trị 38 trong cột đầu tiên của phạm vi A2:C10, sau đó trả về giá trị được chứa trong cột thứ ba của phạm vi và trên cùng hàng làm giá trị tra cứu ("Axel Delgado").
Chữ V trong VLOOKUP đại diện cho vertical (theo chiều dọc). Dùng VLOOKUP thay cho HLOOKUP khi các giá trị so sánh của bạn được đặt trong một cột ở bên trái của dữ liệu mà bạn muốn tìm.
Cú pháp
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Cú pháp hàm VLOOKUP có các đối số dưới đây:
lookup_value Bắt buộc. Các giá trị để tìm kiếm trong cột đầu tiên của bảng hoặc phạm vi. Đối số lookup_value có thể được xem như là một giá trị hoặc một tham chiếu. Nếu giá trị bạn cung cấp cho đối số lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của đối số table_array, VLOOKUP trả về giá trị lỗi #N/A.
table_array Bắt buộc. Phạm vi ô có chứa dữ liệu. Bạn có thể dùng một tham chiếu cho một phạm vi (ví dụ, A2
8), hoặc một tên phạm vi. Các giá trị trong cột đầu tiên của table_array là các giá trị được tìm kiếm bởi lookup_value. Những giá trị này có thể là văn bản, số hoặc giá trị lô-gic. Văn bản chữ hoa và chữ thường tương đương nhau.col_index_num Bắt buộc. Số cột trong đối số table_array mà giá trị khớp phải được trả về từ đó. Một đối số col_index_num của 1 trả về giá trị trong cột đầu tiên trong table_array; một col_index_num của 2 trả về giá trị trong cột thứ hai trong table_array, và cứ như vậy.
Nếu đối số col_index_num là:
Nhỏ hơn 1, VLOOKUP trả về giá trị lỗi #VALUE .
Lớn hơn số lượng cột trong table_array, VLOOKUP trả về giá trị lỗi #REF! .
range_lookup Tùy chọn. Một giá trị lô-gic xác định bạn có muốn VLOOKUP tìm thấy một kết quả khớp chính xác hay kết quả khớp tương đối:
Nếu range_lookup là TRUE hoặc bị bỏ qua, một kết quả khớp chính xác hoặc tương đối được trả về. Nếu một kết quả khớp chính xác không được tìm thấy thì giá trị lớn nhất kế tiếp nhỏ hơn lookup_value được trả về.
QUAN TRỌNG Nếu range_lookup là TRUE hoặc bị bỏ qua, các giá trị ở cột đầu tiên của table_array phải được đặt theo trật tự sắp xếp giảm dần; nếu không, VLOOKUP có thể không trả về giá trị đúng.
Để biết thêm thông tin, vui lòng xem Sắp xếp dữ liệu trong phạm vi hoặc bảng.
Nếu range_lookup là FALSE, các giá trị trong cột đầu tiên của table_array không cần được sắp xếp.
Nếu đối số range_lookup là FALSE, VLOOKUP sẽ chỉ tìm một kết quả khớp chính xác. Nếu có hai hoặc nhiều giá trị trong cột đầu tiên của table_array khớp với lookup_value, giá trị đầu tiên được tìm thấy sẽ được sử dụng. Nếu một kết quả khớp chính xác không được tìm thấy, một giá trị lỗi #N/A được trả về.
Chú thích
Khi tìm kiếm các giá trị văn bản trong cột đầu tiên của table_array, hãy đảm bảo rằng dữ liệu trong cột đầu tiên của table_array không chứa dấu cách trống ở đầu, dấu cách trống ở cuối, việc sử dụng không nhất quán dấu nháy kép thẳng ( ' hoặc " ) và dấu nháy kép cong ( ‘ hoặc “), hoặc các ký tự không in ra. Trong những trường hợp này, VLOOKUP có thể trả về một giá trị không chính xác hoặc không được mong đợi.
Để biết thêm thông tin, vui lòng xem Hàm CLEAN và hàm TRIM.
Khi tìm kiếm các giá trị ngày, hãy bảo đảm dữ liệu trong cột đầu tiên của table_array không được lưu trữ như là các giá trị văn bản. Trong trường hợp này, VLOOKUP có thể trả về một giá trị không đúng hoặc không được mong đợi.
Nếu range_lookup là FALSE và lookup_value là văn bản, bạn có thể sử dụng các ký tự đại diện — dấu chấm hỏi (?) và dấu sao (*) — trong lookup_value. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
Ví dụ
Sao chép dữ liệu của ví dụ trong bảng sau đây và dán vào ô A1 của một trang tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu.ban nha Nam Tu Liem
MẬT ĐỘ ĐỘ NHỚT NHIỆT ĐỘ
0,457 3,55 500
0,525 3,25 400
0,606 2,93 300
0,675 2,75 250
0,746 2,57 200
0,835 2,38 150
0,946 2,17 100
1,09 1,95 50
1,29 1,71 0
Công thức Mô tả Kết quả
=VLOOKUP(1,A2:C10,2) Dùng khớp gần đúng để tìm kiếm giá trị 1 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 1 trong cột A là 0,946 và sau đó trả về giá trị từ cột B trong cùng hàng. 2,17
=VLOOKUP(1,A2:C10,3,TRUE) Dùng khớp gần đúng để tìm kiếm giá trị 1 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 1 trong cột A là 0,946 và sau đó trả về giá trị từ cột C trong cùng hàng. 100
=VLOOKUP(0.7,A2:C10,3,FALSE) Dùng khớp chính xác để tìm giá trị 0,7 trong cột A. Vì không có sự khớp chính xác trong cột A, lỗi được trả về #N/A
=VLOOKUP(0.1,A2:C10,2,TRUE) Dùng khớp gần đúng để tìm giá trị 0,1 trong cột A. Vì 0,1 nhỏ hơn giá trị nhỏ nhất trong cột A, lỗi được trả về. #N/A
=VLOOKUP(2,A2:C10,2,TRUE) Dùng khớp gần đúng để tìm kiếm giá trị 2 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 2 trong cột A là 1,29 và sau đó trả về giá trị từ cột B trong cùng hàng. 1,71
Ví dụ 2
ID MỤC MỤC COST TĂNG GIÁ
ST-340 Ghế đẩy $145,67 30%
BI-567 Yếm dãi $3,56 40%
DI-328 Tả lót $21,45 35%
WI-989 Khăn tay $5,12 40%
AS-469 Máy hút gió $2,56 45%
Công thức Mô tả Kết quả
= VLOOKUP("DI-328", A2
6, 3, FALSE) * (1 + VLOOKUP("DI-328", A26, 4, FALSE)) Tính toán giá bán lẻ của tả lót bằng cách thêm tỷ lệ phần trăm tăng giá vào chi phí. $28,96 = (VLOOKUP("WI-989", A2
6, 3, FALSE) * (1 + VLOOKUP("WI-989", A26, 4, FALSE))) * (1 - 20%) Tính toán giá bán của khăn tay bằng cách trừ đi một phần chiết khấu đã xác định trong giá bán lẻ. $5,73 = IF(VLOOKUP(A2, A2
6, 3, FALSE) >= 20, "Giá tăng thêm là " & 100 * VLOOKUP(A2, A26, 4, FALSE) &"%", "Chi phí dưới $20,00") Nếu chi phí của một mặt hàng lớn hơn hoặc bằng $20,00, sẽ hiển thị chuỗi "Giá tăng thêm là nn%"; nếu không, sẽ hiển thị chuỗi "Chi phí dưới $20,00" Giá tăng thêm là 30% = IF(VLOOKUP(A3, A2
6, 3, FALSE) >= 20, "Giá tăng thêm là: " & 100 * VLOOKUP(A3, A26, 4, FALSE) &"%", "Chi phí là $" & VLOOKUP(A3, A26, 3, FALSE)) Nếu chi phí của một mặt hàng lớn hơn hoặc bằng $20,00, sẽ hiển thị chuỗi Giá tăng thêm là nn%"; nếu không, sẽ hiển thị chuỗi Chi phí là $n.nn" Chi phí là $3,56 Ví dụ 3
ID HỌ TÊN CHỨC DANH NGÀY SINH
1 Davis Sara Đại diện Bán hàng 08/12/68
2 Fontana Olivier Phó chủ tịch Kinh doanh 19/02/1952
3 Leal Karina Đại diện Bán hàng 30/08/1963
4 Patten Michael Đại diện Bán hàng 19/09/1958
5 Burke Brian Quản lý Bán hàng 04/03/1955
6 Sousa Luis Đại diện Bán hàng 02/07/1963
Công thức Mô tả Kết quả
=INT(YEARFRAC(DATE(2004,6,30), VLOOKUP(5,A2:E7,5, FALSE), 1)) Đối với năm tài chính 2004, tìm tuổi của nhân viên có ID là 5. Dùng hàm YEARFRAC để trừ đi ngày sinh từ ngày kết thúc năm tài chính và hiển thị kết quả là một số nguyên bằng hàm INT. 49
=IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "Không tìm thấy nhân viên", VLOOKUP(5,A2:E7,2,FALSE)) Nếu có một nhân viên có ID bằng 5, sẽ hiển thị họ của nhân viên; nếu không, sẽ hiển thị thông báo "Không tìm thấy nhân viên".
Hàm ISNA trả về giá trị TRUE khi hàm VLOOKUP trả về giá trị lỗi #N/A. Burke
=IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "Không tìm thấy nhân viên", VLOOKUP(15,A3:E8,2,FALSE)) Nếu có một nhân viên có ID bằng 15, sẽ hiển thị họ của nhân viên; nếu không, sẽ hiển thị thông báo "Không tìm thấy nhân viên".
Hàm ISNA trả về một giá trị TRUE khi hàm VLOOKUP trả về giá trị lỗi #N/A. Không tìm thấy nhân viên
=VLOOKUP(4,A2:E7,3,FALSE) & " " & VLOOKUP(4,A2:E7,2,FALSE) & " là " & VLOOKUP(4,A2:E7,4,FALSE) Đối với nhân viên có ID bằng 4, ghép nối (kết hợp) các giá trị bao gồm ba ô trong một câu hoàn chỉnh. Michael Patten là một Đại diện Bán hàng.
