Kết Hợp Hàm Vlookup Và If

Cách dùng hàm Vlookup kết hợp trong Excel khá nhiều dạng, linh hoạt. Trên thực tế, Khi những yêu cầu các bước phức tạp yên cầu phải tùy biến, phối kết hợp nhiều hàm cùng nhau thì mới giải quyết và xử lý được yêu cầu. Vậy từ bây giờ cùng annexsport.store 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 quý giá trong cột ko kể cùng phía bên trái của bảng và kế tiếp trả về một giá trị trong và một hàng xuất phát điểm từ một cột khác mà các bạn chỉ định.

Bạn đang xem: Kết hợp hàm vlookup và if

Cú pháp: 

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: cần sử dụng IF để so sánh giá trị vào hàm VLOOKUP

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

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

Bước 1: Kích con chuột vào địa chỉ cửa hàng ô F3Bước 2: Áp dụng vào hàm ta nhấn được phương pháp tính: =IF(VLOOKUP(E3,B3:C9,2,FALSE)>10000,“Yes”,“No”)Bước 3: dìm Enter được hiệu quả trả về như hình phía bên dưới

*

Hình: Hàm VLOOKUP phối kết hợp với IF để đối chiếu giá tri

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

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

Mô tả: 

Bước 1: Gán quý hiếm Jam mang lại ô E3. Bước 2: Để tìm kiếm doanh số, hãy gán công thức: =VLOOKUP(E3,B3:C9,2,FALSE) mang đến ô F3.

Khi thực hiện ngừng sẽ trả về lỗi #N/A. Tức là tên Jam ko tồn tại trong số ô B3:C9.

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

Bước 1: Kích loài chuột vào ô F3Bướ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 phù hợp với IF để xử lý lỗi

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

Ví dụ 3: Dùng IF nhằm tra cứu vớt hai giá bán trị trong hàm VLOOKUP

Xét ví dụ, những ô B2:B7 chứa giá trị mang đến các thành phầm ở nhị hàng không giống nhau.

Để tìm cực hiếm của thành phầm trong ô F3, bạn triển khai như sau:

Bước 1: Kích chuột vào ô H3Bước 2: vào ô H3 nhập bí quyết 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ình : Hàm VLOOKUP kết hợp với IF nhằm tra cứu vớt hai giá bán trị

Khi tiến hành cách này đang trả lại $ 2000. Hàm IF soát sổ xem quý hiếm trong ô G3 là shop 1 xuất xắc 2. Theo điều kiện này, VLOOKUP kế tiếp trả về giá khớp ứng cho sản phẩm.

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

a) HÀM LEFT : Hàm tách ký tự trường đoản cú phía phía trái với con số ký tự lấy ra do bản thân quy định.

Cú pháp: 

= LEFT(text, num_chars)Text: đoạn văn bạn dạng cần bóc tách ký tựNum_chars: số cam kết tự muốn bóc tích từ bên trái. Nếu như không nhập thì vẫn tự nhận cực hiếm là 1VÍ DỤ HÀM VLOOKUP VỚI HÀM LEFT :Chúng ta gồm một bảng tài liệu như hình dưới

*

Hình : Ví dụ Hàm VLOOKUP với 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à giảm 2 cam kết tự nghỉ ngơi ô B4

Sau đó ấn Enter. Tác dụng hiện ra là 2 ký kết tự CN đã được cắt ra từ bỏ chuỗi cam kết tự CNTT1.

*

Hình :Hàm LEFT vào Excel

Bước 2: chúng ta đ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, là sản phẩm tự cột cần lấy quý giá 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 kia ấ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 thích hợp Hàm LEFT với Hàm VLOOKUP

Bước 3: Nhưng làm cho như vậy sẽ rất mất thời gian, bọn họ sẽ triển khai kết vừa lòng 2 hàm là Vlookup và hàm Left cùng với nhau. Trên ô E4 nhập bí quyết =VLOOKUP(LEFT(B4,2),$G$4:$H$8,2,FALSE) và thừa nhận Enter.

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

Sau kia ấn Enter. Hiệu quả hiện ra là Tên ngành tương ứng với Mã Ngành.

Xem thêm: Chèn Số Thứ Tự Trong Excel Siêu Nhanh, Đơn Giản, Tự Động Đánh Số Các Hàng

*

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

Bước 4: Các chúng ta kéo trường đoản cú ô C4, D4, E4 xuống để các ô mặt dưới auto điền cách làm và cho ra công dụng 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í vì bạn chỉ định, dựa vào số lượng ký tự vì bạn chỉ định.

Cú pháp: 

= MID(text, start_num, num_chars)text: Chuỗi ký tự.start_num: Vị trí bước đầu cần cắt chuỗi ký tự.num_chars: Số ký tự phải cắt ra trường đoản cú chuỗi ký tự.c) HÀM RIGHT : Nếu hàm LEFT để tách chuỗi ký kết tự phía bên trái thì hàm RIGHT ngược lại, bóc tách chuỗi cam kết tự từ bỏ bên nên trong một dãy ký kết tự mà người dùng lựa chọn

Cú pháp: 

= RIGHT(text, num_chars)Text: đoạn văn bạn dạng cần tách bóc ký tựNum_chars: số ký tự muốn bóc tách tích từ bên phải. Còn nếu như không nhập thì đã tự nhận quý hiếm 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ự như với hàm SUM về phương diện tính tổng các giá trị. Điểm khác hoàn toàn chính là hàm SUMIF tính tổng chỉ những giá trị đáp ứng tiêu chuẩn mà các bạn đã định rõ.

Và bây giờ, để hiểu bọn họ hay đến với lấy một ví dụ ngay đây. Giả sử, bạn có Table 1: thương hiệu người phân phối và số ID (bảng bắt buộc tìm). Và Table 2 bao gồm cùng số ID và các con số tương quan đến doanh thu . Nhiệm vụ của doanh nghiệp là tra cứu tổng doanh thu của một người khẳng định bằng số ID của họ. Và, có hai nhân tố phức tạp:

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

*

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

Trong trường vừa lòng này, nếu sử dụng hàm Sumif cần yếu xuất ra hiệu quả được vị giá trị doanh thu nằm sinh hoạt cột Mã ID không thuộc bảng Table 2 cùng không liên quan gì mang lại bảng Table 1. Vì vậy cần sử dụng hàm tìm kiếm kiếm Vlookup để dò kiếm tìm mã số ID tương ứng tiếp đến kết phù hợp với hàm SUMIF nhằm 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 cùng Vlookup là tên hàm tính tổng với hàm tìm kiếm theo điều kiện.E:E là vùng được tuyển lựa để chứa các ô điều kiệnI2 là giá bán trị so sánh với cột doanh số, là quý giá dò tìm. Tại trên đây khi bạn đổi khác tên thì cột lợi nhuận cũng biến đổi theo.B2:C10 là trang bị tự cột bắt buộc lấy dự liệu để dò gì mang đến giá trị I2 sống trên.Số 2 là vật dụng tự xuất giá trị, hiển thị lên screen tùy theo cột yêu cầu lấy dữ liệu có mấy cột, bởi cột Mã ID ở phần thứ 2 nên họ đặt là 2.False là phạm vi tìm kiếm kiếm hoàn hảo nhất cho kết quả đúng đắn thay vì sử dụng True cho tác dụng tương đối.

*

Hình: Hàm VLOOKUP phối 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 bí quyết ThanhTien = SoLuong * Gia ), với SoLuong trong bảng 1 với Gia vào bảng 3

Ý tưởng: Để giải quyết và xử lý yêu mong đó: Ta vẫn cần địa thế căn cứ vào MaHang vào bảng 1, triển khai dò vào bảng 2 để lấy TenHang (sử dụng hàm HLOOKUP), kế tiếp lại dùng TenHang từ tác dụng trả về của hàm HLOOKUP bên trên dò kiếm tìm trong bảng 3 để lấy Gia (hàm VLOOKUP) và sau cùng là nhân cùng với SoLuong nhằm ra ThanhTien.

*

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

Bạn có thể làm điều đó dễ dãi hơn với việc tạo cột phụ. Mặc dù nhiên, trong trường hòa hợp này ta đã sử dụng phối kết hợp hàm VLOOKUP với HLOOKUP. Cụ thể ta tất 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ách làm trên như sau: cực hiếm trả về của hàm HLOOKUP là quý hiếm tìm tìm của hàm VLOOKUP.

*

Hình: Hàm VLOOKUP phối hợp hàm HLOOKUP

KẾT LUẬN

Trên đấy là các ví dụ thường gặp gỡ nhất vào việc phối hợp các hàm cùng với hàm VLOOKUP. Chúng ta hãy ghi nhớ nhằm sử dụng cân xứng trong từng hoàn cảnh.

Để có thể sử dụng xuất sắc việc kết hợp những hàm với nhau, chúng ta cần cụ rõ được logic của vấn đề trước, sau đó new xác định áp dụng hàm nào, đặt máy 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 công việc, bọn họ không chỉ nắm rõ được các hàm mà còn yêu cầu sử dụng giỏi cả các công cố của Excel. Ngoài ra còn rất nhiều hàm cải thiện khác như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… luật như Data validation, Pivot table, nguồn Query…

GÓC HỌC TẬP

THAM KHẢO THÊM