VLOOKUP (Hàm VLOOKUP)

Bài viết này mô tả cú pháp công thức và cách dùng hàmVLOOKUP 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.

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:D8), 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 CLEANhà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ụ

Sổ làm việc bên dưới sẽ đưa ra những ví dụ về hàm này. Hãy kiểm tra chúng, rồi thay đổi các công thức hiện có hoặc nhập công thức của riêng bạn để tìm hiểu cách thức hoạt động của hàm này.

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.

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

Để làm việc chuyên sâu hơn với dữ liệu mẫu trong Excel, hãy tải xuống sổ làm việc được nhúng vào máy tính của bạn, rồi mở trong Excel.

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:D6, 3, FALSE) * (1 + VLOOKUP("DI-328", A2:D6, 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:D6, 3, FALSE) * (1 + VLOOKUP("WI-989", A2:D6, 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:D6, 3, FALSE) >= 20, "Giá tăng thêm là " & 100 * VLOOKUP(A2, A2:D6, 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:D6, 3, FALSE) >= 20, "Giá tăng thêm là: " & 100 * VLOOKUP(A3, A2:D6, 4, FALSE) &"%", "Chi phí là $" & VLOOKUP(A3, A2:D6, 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.

Đầu Trang

Áp dụng Cho: Excel 2013, Excel Online



Thông tin này có hữu ích không?

Không

Chúng tôi có thể cải thiện như thế nào?

255 ký tự còn lại

Để bảo vệ sự riêng tư của bạn, vui lòng không đưa thông tin liên hệ trong phản hồi của bạn. Xem xét chính sách về quyền riêng tư.

Cảm ơn phản hồi của bạn!

Tài nguyên hỗ trợ

Thay đổi ngôn ngữ