1. Tổng quan:

VLOOKUP là một hàm Excel luôn luôn được mọi người tìm hiểu khi đến với Excel. Vì sao ư? Đơn giản vì nó là hàm dễ viết nhất khi bạn cần tìm kiếm một giá trị tương ứng nào đó mà bạn muốn trong 1 bảng dữ liệu. Ví dụ như trong ô G2, mình sẽ đặt công thức VLOOKUP để từ mã nhân viên là NV001 mình sẽ tìm ra được tên của nhân viên đó. Vậy khi mình đã lấy ra được tên của mã nhân viên đó thì mình có thể lấy ra năm sinh hay phòng ban được hay không? Câu trả lời đương nhiên là được.

Trong Excel hiện tại có 4 hàm có chữ LOOKUP lần lượt là: LOOKUP, VLOOKUP, HLOOKUP và XLOOKUP. Mỗi hàm này có cú pháp và cách sử dụng khác nhau nhưng trong bài toán này chúng ta sẽ chỉ tập trung vào hàm VLOOKUP mà thôi.

V là từ viết tắt của Vertical (Được dịch ra là theo chiều dọc). Vì thế cách tìm kiếm của hàm VLOOKUP luôn là đi trong cột đầu tiên của vùng dữ liệu được chúng ta đưa vào table_array. Sau khi tìm được dòng chứa lookup_value hay giá trị chúng ta muốn tìm kiếm thì nó sẽ đi sang bên phải và tìm ra giá trị mà chúng ta muốn dựa vào tham số col_index_num (được dịch ra là số cột)

2. Cú pháp:

Hàm VLOOKUP có cú pháp như sau.

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

Hàm VLOOKUP có 4 tham số như sau.

Lookup_value: là một tham số bắt buộc. Giá trị cần tìm kiếm (Như ví dụ trên của chúng ta thì lookup_value đang là mã nhân viên trong ô F1)

Table_Array: là một tham số bắt buộc. Vùng dữ liệu cần tìm kiếm (Đây chính là vùng dữ liệu mà bạn cần tìm kiếm và lấy giá trị trong đó. Table_array phải đảm bảo 2 điều kiện, đó là cột đầu tiên của vùng dữ liệu phải chứa giá trị cần tìm kiếm, nếu không thì kết quả mà chúng ta nhận được sẽ là #N/A. Điều kiện thứ 2 đó là vùng dữ liệu cần tìm kiếm cần phải chứa giá trị mà chúng ta muốn đúng không nào?)

Col_index_num: là một tham số bắt buộc. Cột dữ liệu chúng ta muốn lấy ra kết quả (Đối với tham số này thì chỉ có 1 yêu cầu duy nhất thôi. Đó là Col_Index_num không thể lớn hơn số cột mà vùng dữ liệu table_array có. Ví dụ như công thức ở trên, vùng dữ liệu chỉ có 5 cột. Nếu bạn muốn lấy cột 6 thì khi đó Excel sẽ dỗi bạn đó và trả kết quả là #REF (tức lỗi vùng dữ liệu))

Range_lookup: là một tham số tuỳ chọn. Phương thức tìm kiếm (Hàm VLOOKUP cung cấp 2 phương thức tìm kiếm là Approximate Match <tìm kiếm tương đối> và Exact Match <Tìm kiếm chính xác>. Mặc định của hàm VLOOKUP thì phương thức tìm kiếm sẽ là Approximate Match hay còn có thể viết bằng TRUE hoặc 1. Còn phương thức tìm kiếm chính xác sẽ được viết bằng FALSE hoặc 0. Tạm thời chúng ta sẽ chưa phân biệt sự khác nhau này mà sẽ tới vào phần sau của bài viết).

3. VLOOKUP sẽ chỉ tìm kiếm được các giá trị nằm bên tay phải của lookup_value?

Có thể nói luận điểm trên là không đúng vì hàm VLOOKUP có thể tìm kiếm các giá trị bên tay trái của lookup_value bằng cách sử dụng thêm hàm CHOOSE và công thức mảng như hình dưới đây.

Tuy nhiên cách viết trên sẽ rất khó cho người mới vì thế khi đã muốn tìm kiếm các giá trị bên trái của lookup_value thì các bạn sẽ tham khảo INDEX kết hợp với MATCH của mình nhé.

Ví dụ trên chỉ chứng minh 1 điều đó là VLOOKUP hoàn toàn có thể tìm kiếm giá trị bên trái của nó mà thôi.

4. VLOOKUP lấy ra kết quả dựa vào số cột mà chúng ta muốn.

Hàm VLOOKUP sẽ lấy ra giá trị tương ứng nằm ở cột mà bạn muốn. Nhưng số cột này được tính như thế nào?

Ví dụ như bạn cần lấy ra cột họ tên thì đó là cột B. Mà cột B là cột số 2 trong bảng tính đúng không nào? Vậy chúng ta cứ dựa vào đó để lấy ra số cột nhé. Theo bạn điều đó đúng hay sai?

Đương nhiên là sai rồi. Cột mà bạn muốn lấy sẽ được tính là cột thứ 2 của vùng dữ liệu từ A2:D6. Và cột thứ của vùng dữ liệu đó chính là cột B. Các bạn đừng nhầm khái niệm này nhé.

Thêm nữa tại sao trong công thức VLOOKUP ở hình trên lại có sự xuất hiện của hàm ROW mà không phải là các con số 2, 3, 4. Đơn giản vì nếu dùng hàm ROW thì khi kéo xuống bạn sẽ thu được các kết quả lần lượt là ROW(2:2) => ROW(4:4) trả về kết quả là 2, 3, 4. Nhưng nếu bạn dùng các số 2, 3, 4 thì sao. Khi đó đơn giản bạn sẽ phải sửa tay từng công thức. Ở đây chỉ phải sửa 3 lần thì có vẻ OK đúng không nào. Nhưng nếu là 20 dòng thì sao, mình tin đó là bạn sẽ câu trả lời khác rồi đấy. Bản thân mình là một người rất lười nên việc gì mình có thể làm nhanh thì mình sẽ rút ngắn nó đến hết mức có thể.

5. VLOOKUP có 2 chế độ tìm kiếm, tuyệt đối và tương đối.

Như đã nói ở trên, mình đã khẳng định là VLOOKUP có 2 kiểu tìm kiếm rồi, tuyệt đối (FALSE hoặc 0) và tương đối (TRUE hoặc 1). Vậy chúng khác nhau như thế nào?

Với FALSE hoặc 0 thì mình tin là các bạn dùng đến nó quá nhiều trong Excel rồi. Đó là tìm kiếm chính xác. Ví dụ như khi bạn đi đón con đi, bạn vào lớp hỏi cô giáo cô cho tôi đón cháu Nguyễn Văn A. Khi đó cô giáo sẽ trả cho bạn cháu Nguyễn Văn B chứ không thể nào trả cho bạn cháu Nguyễn Văn A1 được. Cái này gọi là tìm kiếm chính xác một cách tuyệt đối.

Vậy còn tìm kiếm tương đối thì sao? Nên nhớ mặc định của hàm VLOOKUP thì kiểu tìm kiếm sẽ là tìm kiếm tương đối nhé.

Để làm rõ vấn đề trên, chúng ta đến với ví dụ nho nhỏ dưới đây.

Chúng ta có 1 ví dụ nho nhỏ dưới đây là 1 công ty nho nhỏ cần xét thưởng doanh số cho nhân viên kinh doanh. Các mức thưởng doanh số được giám đốc công ty ghi bên phía tay phải. Đây là lúc mà chúng ta sẽ sử dụng tới việc tìm kiếm tương đối của hàm VLOOKUP.

Đầu tiên các bạn cần chú ý tới bảng phụ dùng để tìm kiếm mình đã lập bên tay phải. Đó là cột đầu tiên dùng để tìm kiếm giá trị lookup_value trong đó cần được sắp xếp từ bé tới lớn. Đây là điều kiện bắt buộc khi sử dụng tìm kiếm tương đối. Câu hỏi nhỏ mình đặt ra ở đây là đối với tìm kiếm tuyệt đối thì có cần sắp xếp dữ liệu như thế không?

Câu trả lời là không, bạn sắp xếp được thì quá tốt nhưng khi đã tìm kiếm một cách tuyệt đối thì đây là điều không cần thiết.

Câu hỏi tiếp theo là tìm kiếm tương đối sẽ tìm kiếm như thế nào?

Câu trả lời là hàm VLOOKUP sẽ luôn tìm các giá trị nhỏ hơn lookup_value nhưng lớn nhất. Nói ngắn gọn là tìm ra cận dưới của lookup_value trong cột đầu tiên của bảng Table_array.

Vì thế khi ta tìm % hoa hồng của doanh số 500 triệu. Ta sẽ có số cận dưới của nó là 450 triệu. Sau đó ta lấy giá trị trong cột 2 tương ứng thì thu được % hoa hồng là 40%.

Vậy điều gì sẽ xảy ra khi lookup_value không có cận dưới? Khi đó chúng ta sẽ không tìm thấy giá trị mà chúng ta muốn và nhận được kết quả là #N/A.

6. VLOOKUP và lỗi #N/A

Khi bạn dùng hàm VLOOKUP và nhận được lỗi #N/A, bước đầu tiên cần làm đó là đừng lo lắng vì việc gì cũng có cách giải quyết. Dưới đây mình sẽ tổng hợp 1 số lỗi dẫn khi sử dụng hàm VLOOKUP mà dẫn tới #N/A nhé.

- Giá trị cần tìm kiếm lookup_value không tồn tại trong cột đầu tiên của vùng dữ liệu (table_array)

- Giá trị cần tìm kiếm không khớp với giá trị trong cột đầu tiên của vùng dữ liệu. Có thể là bạn tìm bằng mắt thì thấy nó đó nhưng hãy cẩn thận vì 1 số giá trị bạn đã ghi thừa 1 dấu cách chẳng hạn ở đâu đó. Qua đó Excel coi 2 giá trị đó là khác nhau mà không tìm ra được kết quả. Cái này đến từ lỗi nhập liệu nên bạn phải giải quyết nó bằng sửa tay hoặc hàm TRIM nhé.

- Chế độ tìm kiếm bạn đang để ở tuyệt đối trong khi phải tìm kiếm tương đối hoặc ngược lại. Các bạn cần ghi nhớ một điều là nếu bạn bỏ qua tham số range_lookup thì VLOOKUP sẽ tìm kiếm tương đối nhé.

- Table_Array được đưa vào không đúng, điều này khiến giá trị cần tìm kiếm không thể được tìm thấy ở cột đầu tiên của Table_array.

- Bạn Copy công thức VLOOKUP nhưng quên cố định Table Array hoặc lookup_array, … Đây là lỗi mà các bạn gặp rất nhiều. Lời khuyên là khi nào thấy tham số gợi ý nào của Excel mà kết thúc bằng array, ref hay reference thì bạn hãy cố định nó vào bằng cách ấn phím F4 nhé.

Vậy khi bị dính lỗi #N/A mà bạn biết chắc chắn nó không có và muốn bắt lỗi nó để thay bằng 1 giá trị khác thì chúng ta có cách nào không?

Câu trả lời đó là hàm IFNA và IFERROR.

Tuy nhiên có 1 lưu ý là hàm IFERROR mới xuất hiện từ bản Excel 2010, còn IFNA là phiên bản Excel 2013. Cú pháp 2 hàm này tuy giống nhau nhưng cách dùng hơi khác 1 chút. Với IFNA chỉ bắt lỗi #N/A còn IFERROR bắt toàn bộ lỗi trong Excel nhé các bạn.

Các bạn có thể tham khảo cách dùng kết hợp hàm IFERROR và IFNA trong ví dụ ngay dưới đây.

7. Tìm kiếm nhiều điều kiện bằng hàm VLOOKUP

Ở các ví dụ trên chúng ta vẫn tìm kiếm giá trị chúng ta muốn bằng hàm VLOOKUP nhưng lưu ý là việc tìm kiếm nãy giờ của chúng ta chỉ có một điều kiện mà thôi. Vậy trong trường hợp tìm kiếm với hơn 1 điều kiện thì sẽ như thế nào?

Khi đó với hàm VLOOKUP chúng ta sẽ tạo ra thêm 1 cột phụ ngoài cùng bên tay trái của bảng dữ liệu để hỗ trợ việc viết công thức nhé. Đương nhiên bạn cũng có thể chèn bên tay phải nhưng khi đó sẽ cần có hàm CHOOSE để tìm kiếm sang bên trái như mục 3 ở trên mình đã chia sẻ nhé.

Trong trường hợp các bạn không muốn làm cột phụ thì có thể tham khảo phương án sử dụng VLOOKUP kết hợp hàm CHOOSE như hình vẽ trên nhé.

8. Tìm kiếm giá trị gần đúng với ký tự đại diện trong Excel

Hàm VLOOKUP cũng hỗ trợ việc sử dụng ký tự đại diện trong Excel. Ví dụ như bạn muốn tìm ra xem bạn tên là Vũ có trong danh sách trên thì bạn có thể để giá trị cần tìm kiếm sẽ là *Vũ là được. Các bạn nhớ để chuỗi này trong ngoặc kép giúp mình nhé. Dấu * đại diện cho một ký tự, một chuỗi ký tự hoặc không có gì trước chữ Vũ. Vì thế nếu mình viết là *Vũ thì mình biết được bạn đó tên là Vũ còn trước đó là gì thì mình không hề quan tâm.

9. Sử dụng VLOOKUP lấy ra nhiều kết quả

Như các bạn đã biết đó là hàm VLOOKUP luôn chỉ lấy ra kết quả đầu tiên mà nó tìm được thôi. Nhưng nếu bạn cần lấy ra nhiều kết quả thoả mãn điều kiện thì bạn sẽ làm thế nào.

Câu trả lời là bạn sẽ cần thêm 1 cột phụ với công thức ở ô A2 như mình đã viết. Lý do của việc này đó là chúng ta sẽ cần tạo ra một cột phòng ban với ID không bị trùng lặp. Sau đó chúng ta sẽ trích lọc các ID của phòng kỹ thuật ra rồi dùng hàm VLOOKUP như ô B10 để thu được kết quả như hình trên mà thôi.

Để tổng quát hơn và không dùng cột phụ thì các bạn sẽ đọc trong bài viết về INDEX và MATCH của mình nhé.

Chúc các bạn thành công với hàm VLOOKUP nhé.