Hàm VLOOKUP kết hợp IF, LEFT, MID, RIGHT, HLOOKUP - Excel - Excel - The first knowledge sharing application in Vietnam

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!!

Mục lục bài viết

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
Bạn đang đọc: Hàm VLOOKUP kết hợp IF, LEFT, MID, RIGHT, HLOOKUP - Excel - Excel 
, 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 thi :

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 tác dụng trả về như hình phía bên dưới

Hàm VLOOKUP- IF để so sánh giá trị

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 hiệu quả trả về như hình dưới

Hàm IF và VLOOKUP để xử lý lỗ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 hiệu quả như hình dưới

Hàm IF kết hợp VLOOKUP để tra cứu hai giá trị

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 phối 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 tài liệu như hình dưới

Ví dụ hàm Vlookup và hàm LEFT

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

Xem thêm: 30+ Kí Tự Đặc Biệt Gạch chéo ❤️✔️ Tên Gạch chéo đẹp – KiTuHay.Com

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àm LEFT trong Excel

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àm Vlookup- LEFT trong Excel

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à tất cả 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àm Vlookup kết hợp LEFT

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àm Vlookup kết hợp Hàm LEFT

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

b) HÀM MID : Hàm cắt chuỗi lượng ký tự cụ thể từ một chuỗi văn bản, bắt đầu từ vị trí do bạn chỉ định, dựa vào số lượng ký tự do bạn chỉ đị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à giờ đây, để hiểu tất cả 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à những số lượng tương quan đến doanh thu. Nhiệm vụ của bạn là tìm tổng doanh thu của một người xác lập bằng số ID của họ. Và, có hai tác nhân 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.

Ví dụ Vlookup và SumIF

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 hề xuất ra tác dụng được vì giá trị doanh thu nằm ở cột Mã ID không thuộc bảng Table 2 và không tương 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 đó tích hợp với hàm SUMIF để tính tổng với điều kiện kèm theo là mã ID thỏa mãn nhu cầu .

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àm vlookup kết hợp hàm sumif

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.

ví dụ vlookup lồng hlookup

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,USD C USD 11 : USD F $ 12,2,FALSE),USD B USD 15 : USD D $ 18,3,FALSE)

Bạn hoàn toà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àm Vlookup lồng hàm Hlookup

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

KẾT LUẬN

Trên đây là những ví dụ thường gặp nhất trong việc tích hợp những hàm với hàm VLOOKUP. Các bạn hãy ghi nhớ để sử dụng tương thích trong từng thực trạng .Để hoàn toàn có thể sử dụng tốt việc phối hợp những hàm với nhau, tất cả chúng ta cần nắm rõ được logic của yếu tố trước, sau đó mới xác lập sử dụng hàm nào, đặt thứ tự những hàm tại vị trí nào .Để hoàn toàn có thể ứng dụng tốt Excel vào trong việc làm, tất cả chúng ta không chỉ nắm vững được những hàm mà còn phải sử dụng tốt cả những 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 fanpagehttps://www.facebook.com/HocExcelCoBa…và nhóm luận bà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 hàng loạt bài giảng Học Excel tại đây :https://goo.gl/IqvnjK

THAM KHẢO THÊM