Hàm VLOOKUP Kết Hợp IF, LEFT, MID, RIGHT, HLOOKUP - Excel

Cách dùng hàm Vlookup kết hợp trong Excel khá đa dạng, linh hoạt. Trên thực tế, Khi các yêu cầu công việc phức tạp đòi hỏi phải tùy biến, kết hợp nhiều hàm với nhau thì mới giải quyết được yêu cầu. Vậy hôm nay cùng Hocexcelcoban hiểu cách kết hợp hàm Vlookup!!

1. CẤU TRÚC HÀM VLOOKUP:

Hàm VLOOKUP (Tra cứu dọc) tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng và sau đó trả về một giá trị trong cùng một hàng từ một cột khác mà bạn chỉ định.

Cú pháp: 

=VLOOKUP(Lookup_value, Table, Col_index_num, [Range_lookup])

• Lookup_value – Giá trị cần tìm trong cột đầu tiên của bảng.
• Table – Bảng để truy xuất một giá trị.
• Col_index – Cột trong bảng để truy xuất một giá trị.
• range_lookup – [tùy chọn] TRUE = đối sánh gần đúng (mặc định). FALSE = đối sánh chính xác

– Bạn có thể xem thêm về Hàm Vlookup tại đây: Học Excel cơ bản

2. HÀM VLOOKUP KẾT HỢP HÀM TRONG EXCEL

2.1 SỰ KẾT HỢP CỦA HÀM VLOOKUP VỚI HÀM IF 

Ví dụ 1: Dùng IF để so sánh giá trị trong hàm VLOOKUP

Mô tả: Dựa vào cột dữ liệu có trong các ô B3: C9, hãy tìm hiểu xem tên James được đề cập trong ô E3 có phần thưởng dựa trên doanh số lớn hơn 10000$ hay không?

Các bước thực hiện:

• Bước 1: Kích chuột vào địa chỉ ô F3
• Bước 2: Áp dụng vào hàm ta nhận được công thức tính:                          =IF(VLOOKUP(E3,B3:C9,2,FALSE)>10000,“Yes”,“No”)
• Bước 3: Nhấn Enter được kết quả trả về như hình phía bên dưới

Hình: Hàm VLOOKUP kết hợp với IF để so sánh giá tri

Ví dụ 2: Dùng IF để xử lý lỗi giá trị trong hàm VLOOKUP

Yêu cầu: Sử dụng hàm VLOOKUP kết hợp với hàm IF để tìm ra các lỗi

Mô tả: 

• Bước 1: Gán giá trị Jam cho ô E3. 
• Bước 2: Để tìm doanh số, hãy gán công thức: =VLOOKUP(E3,B3:C9,2,FALSE) cho ô F3.

Khi thực hiện xong sẽ trả về lỗi #N/A. Có nghĩa là tên Jam không tồn tại trong các ô B3:C9.

Vì vậy, Để xử lý lỗi này, ta thực hiện bằng cách lồng hàm VLOOKUP và ISNA vào bên trong hàm IF.

• Bước 1: Kích chuột vào ô F3
• Bước 2: Nhập công thức:                                                                                            =IF(ISNA(VLOOKUP(E3,B3:C9,2,FALSE)), “Name not found”, VLOOKUP(E3,B3:C9,2,FALSE)) vào ô F3.
• Bước 3: Nhấn Enter được kết quả trả về như hình dưới

Hình : Hàm VLOOKUP kết hợp với IF để xử lý lỗi

Cách làm này sẽ trả lại tên không tìm thấy. Sử dụng hàm ISNA sẽ giúp kiểm tra xem kết quả của VLOOKUP có phải bị lỗi #N/A hay không và thực hiện điều kiện IF tương ứng. Bạn có thể đặt tin nhắn văn bản khác hoặc thậm chí một 0 hoặc trống ( “” ) như đầu ra.

Ví dụ 3: Dùng IF để tra cứu hai giá trị trong hàm VLOOKUP

Xét ví dụ, các ô B2:B7 chứa giá trị cho các sản phẩm ở hai hàng khác nhau.

Để tìm giá trị của sản phẩm trong ô F3, bạn thực hiện như sau:

• Bước 1: Kích chuột vào ô H3
• Bước 2: Trong ô H3 nhập công thức sau:                                                                                                                          =IF(G3= “Shop1”,VLOOKUP(F3,B2:D7,2,FALSE),VLOOKUP(F3,B2:D7,3,FALSE))
• Bước 3: Ấn Enter được kết quả như hình dưới

Hình : Hàm VLOOKUP kết hợp với IF để tra cứu hai giá trị

Khi thực hiện cách này  sẽ trả lại $ 2000. Hàm IF kiểm tra xem giá trị trong ô G3 là Cửa hàng 1 hay 2. Theo điều kiện này, VLOOKUP sau đó trả về giá tương ứng cho sản phẩm.

Tham khảo thêm: Hàm VLOOKUP kết hợp hàm IF trong Excel

2.2 HÀM VLOOKUP KẾT HỢP HÀM LEFT, MID, RIGHT

a) HÀM LEFT : Hàm tách ký tự từ phía bên trái với số lượng ký tự lấy ra do mình quy định.

Cú pháp: 

= LEFT(text, num_chars)

• Text: đoạn văn bản cần tách ký tự
• Num_chars: số ký tự muốn tách tích từ bên trái. Nếu không nhập thì sẽ tự nhận giá trị là 1

VÍ DỤ HÀM VLOOKUP VỚI HÀM LEFT :

Chúng ta có một bảng dữ liệu như hình dưới

Hình : Ví dụ Hàm VLOOKUP và Hàm LEFT

Chúng ta sẽ sử dụng hàm LEFT để lấy Mã ngành từ cột Lớp.

Bước 1: Điền vào ô C4 công thức: =LEFT(B4,2). Ý nghĩa của công thức trên là cắt 2 ký tự ở ô B4

Sau đó ấn Enter. Kết quả hiện ra là 2 ký tự CN đã được cắt ra từ chuỗi ký tự CNTT1.

Hình :Hàm LEFT trong Excel

Bước 2: Các bạn điền vào ô D4 công thức: =VLOOKUP($C4,$G$4:$H$8,2,FALSE). $C4 là Giá trị dùng để tìm kiếm, ,$G$4:$H$8 là Vùng dữ liệu tra cứu, 2 là thứ tự cột cần lấy giá trị trong Vùng dữ liệu tra cứu, FALSE là phạm vi tìm kiếm mang tính tuyệt đối.

Sau đó ấn Enter. Kết quả hiện ra là Tên ngành tương ứng với Mã Ngành.

Hình : Kết hợp Hàm LEFT với Hàm VLOOKUP

Bước 3:  Nhưng làm như vậy sẽ khá mất thời gian, chúng ta sẽ tiến hành kết hợp 2 hàm là Vlookup và hàm Left với nhau. Tại ô E4 nhập công thức =VLOOKUP(LEFT(B4,2),$G$4:$H$8,2,FALSE) và nhấn Enter.

Nghĩa là chúng ta sẽ thay giá trị dùng để tìm kiếm $C4 thành LEFT(B4,2).

Sau đó ấn Enter. Kết quả hiện ra là Tên ngành tương ứng với Mã Ngành.

Hình : Hàm VLOOKUP kết hợp Hàm LEFT

Bước 4: Các bạn kéo từ ô C4, D4, E4 xuống để các ô bên dưới tự động điền công thức và cho ra kết quả tương ứng.

Hình : Hàm VLOOKUP kết hợp Hàm LEFT

b) HÀM MID : Hàm cắt chuỗi lượng ký tự cụ thể từ một chuỗi văn bản, bắt đầu từ vị trí do bạn chỉ định, dựa vào số lượng ký tự do bạn chỉ định.

Cú pháp: 

= MID(text, start_num, num_chars)

• text: Chuỗi ký tự.
• start_num: Vị trí bắt đầu cần cắt chuỗi ký tự.
• num_chars: Số ký tự cần cắt ra từ chuỗi ký tự.

c) HÀM RIGHT : Nếu hàm LEFT để tách chuỗi ký tự bên trái thì hàm RIGHT ngược lại, tách chuỗi ký tự từ bên phải trong một dãy ký tự mà người dùng lựa chọn

Cú pháp: 

= RIGHT(text, num_chars)

• Text: đoạn văn bản cần tách ký tự
• Num_chars: số ký tự muốn tách tích từ bên phải. Nếu không nhập thì sẽ tự nhận giá trị là 1

2.3 SỬ DỤNG HÀM VLOOKUP VỚI HÀM SUMIF, SUM

Hàm SUMIF trong Excel thì tương tự với hàm SUM về mặt tính tổng các giá trị. Điểm khác biệt chính là hàm SUMIF tính tổng chỉ các giá trị đáp ứng tiêu chuẩn mà bạn đã định rõ.

Và bây giờ, để hiểu chúng ta hay đến với ví dụ ngay đây. Giả sử, bạn có Table 1: tên người bán và số ID (bảng cần tìm). Và Table 2 có cùng số ID và các con số liên quan đến doanh số . Nhiệm vụ của bạn là tìm tổng doanh số của một người xác định bằng số ID của họ. Và, có hai nhân tố phức tạp:

• Bảng Table 2 chứa nhiều mục có cùng số ID theo thứ tự ngẫu nhiên.
• Bạn không thể thêm cột “Tên người bán” vào bảng Table 2.

Hình: Vlookup kết hợp SUMIF trong Excel

Trong trường hợp này, nếu sử dụng hàm Sumif không thể xuất ra kết quả được vì giá trị doanh số nằm ở cột Mã ID không thuộc bảng Table 2 và không liên quan gì đến bảng Table 1. Do đó cần sử dụng hàm tìm kiếm Vlookup để dò tìm mã số ID tương ứng sau đó kết hợp với hàm SUMIF để tính tổng với điều kiện là mã ID thỏa mãn.

CÔNG THỨC:

=SUMIF(E:E,VLOOKUP(I2,B4:C10,2,FALSE),F:F)

• SumiF và Vlookup là tên hàm tính tổng và hàm tìm kiếm theo điều kiện.
• E:E là vùng được lựa chọn để chứa các ô điều kiện
• I2 là giá trị đối chiếu với cột doanh số, là giá trị dò tìm. Tại đây khi bạn thay đổi tên thì cột doanh số cũng thay đổi theo.
• B2:C10 là thứ tự cột cần lấy dự liệu để dò gì cho giá trị I2 ở trên.
• Số 2 là thứ tự xuất giá trị, hiển thị lên màn hình tùy theo cột cần lấy dữ liệu có mấy cột, vì cột Mã ID ở vị trí thứ 2 nên chúng ta đặt là 2.
• False là phạm vi tìm kiếm tuyệt đối cho kết quả chính xác thay vì sử dụng True cho kết quả tương đối.

Hình: Hàm VLOOKUP kết hợp hàm SUMIF

2.4 HÀM VLOOKUP LỒNG HLOOKUP DÒ TÌM DỮ LIỆU

Chúng ta có ví dụ như sau:

Tính ThanhTien ( biết công thức ThanhTien = SoLuong * Gia ), với SoLuong trong bảng 1 và Gia trong bảng 3

Ý tưởng: Để giải quyết yêu cầu đó: Ta sẽ cần căn cứ vào MaHang trong bảng 1, thực hiện dò trong bảng 2 để lấy TenHang (sử dụng hàm HLOOKUP), sau đó lại dùng TenHang từ kết quả trả về của hàm HLOOKUP trên dò tìm trong bảng 3 để lấy Gia (hàm VLOOKUP) và cuối cùng là nhân với SoLuong để ra ThanhTien.

Hình: Sử dụng Hàm VLOOKUP và HLOOKUP

Bạn có thể làm điều đó dễ dàng hơn với việc tạo cột phụ. Tuy nhiên, trong trường hợp này ta sẽ sử dụng kết hợp hàm VLOOKUP và HLOOKUP. Cụ thể ta có công thức tại ô E5 như sau:

=D5*VLOOKUP(HLOOKUP(B5,$C$11:$F$12,2,FALSE),$B$15:$D$18,3,FALSE)

Bạn có thể hiểu công thức trên như sau: Giá trị trả về của hàm HLOOKUP là giá trị tìm kiếm của hàm VLOOKUP.

Hình: Hàm VLOOKUP kết hợp hàm HLOOKUP

KẾT LUẬN

Trên đây là các ví dụ thường gặp nhất trong việc kết hợp các hàm với hàm VLOOKUP. Các bạn hãy ghi nhớ để sử dụng phù hợp trong từng hoàn cảnh.

Để có thể sử dụng tốt việc kết hợp các hàm với nhau, chúng ta cần nắm rõ được logic của vấn đề trước, sau đó mới xác định sử dụng hàm nào, đặt thứ tự các hàm tại vị trí nào.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Ngoài ra còn rất nhiều hàm nâng cao khác như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Công cụ như Data validation, Pivot table, Power Query…

GÓC HỌC TẬP

• Hỗ trợ giải đáp: tham gia fanpage https://www.facebook.com/HocExcelCoBa… và nhóm thảo luận: https://www.facebook.com/groups/excel…
• Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học: hocexcelcoban 
• Xem toàn bộ bài giảng Học Excel tại đây:https://goo.gl/IqvnjK

THAM KHẢO THÊM

>> Hàm VLOOKUP kết hợp hàm IF >> Hàm VLOOKUP bị lỗi, cách sửa lỗi >> Hàm VLOOKUP kết hợp hàm SUM và SUMIF >> Hàm VLOOKUP là gì? >> Hàm VLOOKUP 2 điều kiện trong Excel  >> Hàm VLOOKUP ngược và cách dùng hàm VLOOKUP ngược trong Excel

VIDEO THỰC HÀNH MỘT SỐ TÌNH HUỐNG THỰC TIỄN :

5 2 đánh giá Đánh giá bài viết hàm lookuphàm vlookup kết hợpkết hợp hàm excel