Hàm vlookup kết hợp hàm left

Cách cần sử dụng hàm Vlookup kết hợp trong Excel tương đối đa dạng, linch hoạt. Trên thực tiễn, khi các yêu cầu các bước phức hợp yên cầu phải tùy trở nên, kết hợp các hàm cùng nhau thì mới có thể xử lý được kinh nghiệm. Vậy hôm nay cùng truemen.vn đọc cách phối kết hợp hàm Vlookup!!

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

Hàm VLOOKUP (Tra cứu giúp dọc) tra cứu kiếm một quý hiếm trong cột ko kể thuộc phía trái của bảng với sau đó trả về một quý hiếm trong và một hàng xuất phát từ một cột khác nhưng bạn chỉ định.

You watching: Hàm vlookup kết hợp hàm left

Cú pháp: 

2. HÀM VLOOKUPhường KẾT HỢPhường HÀM TRONG EXCEL

2.1 SỰ KẾT HỢPhường CỦA HÀM VLOOKUP VỚI HÀM IF 

lấy một ví dụ 1: Dùng IF để so sánh giá trị vào hàm VLOOKUP

Mô tả: Dựa vào cột dữ liệu tất cả trong những ô B3: C9, hãy mày mò coi thương hiệu James được đề cập trong ô E3 tất cả phần ttận hưởng dựa trên lợi nhuận lớn hơn 10000$ xuất xắc không?

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

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

*

Hình: Hàm VLOOKUPhường. phối kết hợp với IF nhằm đối chiếu giá tri

ví dụ như 2: Dùng IF nhằm xử lý lỗi quý hiếm trong hàm VLOOKUP

Yêu cầu: Sử dụng hàm VLOOKUP kết phù hợp với hàm IF để đưa ra những lỗi

Mô tả: 

Cách 1: Gán quý hiếm Jam cho ô E3. Cách 2: Để tìm lợi nhuận, hãy gán công thức: =VLOOKUP(E3,B3:C9,2,FALSE) đến ô F3.

lúc tiến hành chấm dứt vẫn trả về lỗi #N/A. Tức là tên Jam không mãi sau trong các ô B3:C9.

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

Bước 1: Kích 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 tác dụng trả về nlỗi hình dưới

*

Hình : Hàm VLOOKUP. kết hợp với IF nhằm giải pháp xử lý lỗi

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

ví dụ như 3: Dùng IF nhằm tra cứu vãn nhì giá bán trị vào hàm VLOOKUP

Xét ví dụ, các ô B2:B7 cất quý giá cho các thành phầm ngơi nghỉ nhị sản phẩm không giống nhau.

Để search giá trị của sản phẩm vào ô F3, bạn thực hiện nhỏng sau:

Bước 1: Kích chuột vào ô H3Bước 2: Trong ô H3 nhập phương pháp sau: =IF(G3= “Shop1”,VLOOKUP(F3,B2:D7,2,FALSE),VLOOKUP(F3,B2:D7,3,FALSE))Bước 3: Ấn Enter được công dụng nlỗi hình dưới

*

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

Lúc triển khai biện pháp này sẽ trả lại $ 2000. Hàm IF đánh giá xem quý giá trong ô G3 là Cơ sở 1 hay 2. Theo điều kiện này, VLOOKUP tiếp nối trả về giá chỉ khớp ứng mang đến sản phẩm.

2.2 HÀM VLOOKUPhường KẾT HỢPhường. HÀM LEFT, MID, RIGHT

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

Cú pháp: 

= LEFT(text, num_chars)Text: đoạn vnạp năng lượng phiên bản đề xuất bóc tách cam kết tựNum_chars: số cam kết từ mong muốn bóc tách tích tự phía trái. Nếu ko nhập thì vẫn từ bỏ dấn giá trị là 1VÍ DỤ HÀM VLOOKUP VỚI HÀM LEFT :Chúng ta bao gồm một bảng dữ liệu như hình dưới

*

Hình : lấy ví dụ Hàm VLOOKUPhường với Hàm LEFT

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

Cách 1: Điền vào ô C4 công thức: =LEFT(B4,2). Ý nghĩa của cách làm bên trên là giảm 2 cam kết tự ở ô B4

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

*

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

Cách 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 để kiếm tìm tìm, ,$G$4:$H$8 là Vùng dữ liệu tra cứu vớt, là máy từ bỏ cột buộc phải rước giá trị vào Vùng tài liệu tra cứu vãn, FALSE là phạm vi tìm kiếm mang tính hoàn hảo.

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

*

Hình : Kết thích hợp Hàm LEFT cùng với Hàm VLOOKUP

Bước 3: Nhưng làm cho như thế sẽ khá mất thời gian, bọn họ vẫn triển khai kết phù hợp 2 hàm là Vlookup cùng hàm Left cùng nhau. Tại ô E4 nhập phương pháp =VLOOKUP(LEFT(B4,2),$G$4:$H$8,2,FALSE) cùng nhận Enter.

Nghĩa là họ sẽ vắt cực hiếm dùng làm search tìm $C4 thành LEFT(B4,2).

Sau kia ấn Enter. Kết trái chỉ ra là Tên ngành tương ứng với Mã Ngành.

See more: Cách Đổi Mật Khẩu Wifi Bằng Máy Tính, Điện Thoại Tại Nhà Đơn Giản

*

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

Bước 4: Các bạn kéo từ ô C4, D4, E4 xuống nhằm các ô dưới auto điền công thức và đã tạo 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 uống bản, khởi đầu từ vị trí vày người tiêu dùng ra lệnh, dựa vào số lượng ký tự bởi quý khách hàng sai khiến.

Cú pháp: 

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

Cú pháp: 

= RIGHT(text, num_chars)Text: đoạn văn bản buộc phải tách bóc cam kết tựNum_chars: số ký từ ao ước tách tích trường đoản cú bên đề xuất. Nếu không nhập thì vẫn tự dấn quý giá là 1

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

Hàm SUMIF trong Excel thì tựa như với hàm SUM về phương diện tính tổng các quý giá. Điểm biệt lập chính là hàm SUMIF tính tổng chỉ các quý giá thỏa mãn nhu cầu tiêu chuẩn mà lại các bạn cương quyết.

Và bây chừ, để gọi bọn họ tốt đến cùng với ví dụ tức thì phía trên. Giả sử, các bạn gồm Table 1: tên bạn chào bán và số ID (bảng bắt buộc tìm). Và Table 2 có cùng số ID và các con số liên quan mang lại lợi nhuận . Nhiệm vụ của bạn là tra cứu tổng lợi nhuận của một bạn xác minh bằng số ID của mình. Và, gồm hai yếu tố phức tạp:

Bảng Table 2 chứa nhiều mục bao gồm thuộc số ID theo trang bị từ bỗng dưng.quý khách hàng quan yếu thêm cột “Tên tín đồ bán” vào bảng Table 2.

*

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

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

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

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

*

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

Bạn hoàn toàn có thể làm điều đó dễ ợt rộng với Việc chế tác cột phú. Tuy nhiên, vào ngôi trường hòa hợp này ta sẽ áp dụng phối hợp hàm VLOOKUP. và HLOOKUP. Cụ thể ta tất cả bí quyết trên ô E5 nlỗi sau:

=D5*VLOOKUP(HLOOKUP(B5,$C$11:$F$12,2,FALSE),$B$15:$D$18,3,FALSE)Bạn hoàn toàn có thể hiểu cách làm trên nlỗi sau: Giá trị trả về của hàm HLOOKUP.. là quý hiếm tìm kiếm của hàm VLOOKUP..

*

Hình: Hàm VLOOKUPhường phối hợp hàm HLOOKUP

KẾT LUẬN

Trên đây là những ví dụ thường chạm chán độc nhất vào Việc phối hợp các hàm cùng với hàm VLOOKUP. Các bạn hãy ghi nhớ nhằm thực hiện tương xứng trong từng yếu tố hoàn cảnh.

See more: Phần Mềm Chế Ảnh Trên Máy Tính, Pc Miễn Phí, Tốt Nhất Hiện Nay

Để hoàn toàn có thể thực hiện xuất sắc việc phối hợp những hàm cùng nhau, bọn họ buộc phải thay rõ được xúc tích và ngắn gọn của vấn đề trước, sau kia new xác định sử dụng hàm nào, đặt thứ tự những hàm tại địa điểm nào.

Để hoàn toàn có thể áp dụng tốt Excel vào vào quá trình, họ không những nắm vững được các hàm ngoại giả đề nghị thực hiện tốt cả các lý lẽ của Excel. Dường như còn không hề ít hàm cải thiện khác như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Công vắt nlỗi Data validation, Pivot table, nguồn Query…

GÓC HỌC TẬP

THAM KHẢO THÊM