Cách dùng hàm Vlookup kết hợp vào Excel khá nhiều chủng loại, linc hoạt. Trên thực tiễn, lúc những từng trải công việc phức tạp yên cầu đề xuất tùy đổi thay, kết hợp những hàm cùng nhau thì mới giải quyết được những hiểu biết. Vậy bây giờ thuộc clear-elida.com gọi biện pháp 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) kiếm tìm tìm một giá trị vào cột kế bên thuộc phía trái của bảng cùng sau đó trả về một quý hiếm trong cùng một mặt hàng xuất phát từ 1 cột khác mà lại chúng ta chỉ định và hướng dẫn.

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

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 VLOOKUPhường VỚI HÀM IF 

ví dụ như 1: Dùng IF nhằm đối chiếu 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á coi tên James được đề cập vào ô E3 tất cả phần thưởng trọn dựa vào doanh thu to 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 liên can ô F3Bước 2: Áp dụng vào hàm ta nhận thấy phương pháp tính: =IF(VLOOKUP(E3,B3:C9,2,FALSE)>10000,“Yes”,“No”)Cách 3: Nhấn Enter được công dụng 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 để so sánh giá chỉ tri

lấy ví dụ 2: Dùng IF để xử trí lỗi giá trị vào hàm VLOOKUP

Yêu cầu: Sử dụng hàm VLOOKUP kết hợp với hàm IF nhằm tìm thấy những lỗi

Mô tả: 

Bước 1: Gán quý hiếm Jam cho ô E3. Cách 2: Để tra cứu doanh thu, hãy gán công thức: =VLOOKUP(E3,B3:C9,2,FALSE) đến ô F3.

lúc thực hiện chấm dứt đã trả về lỗi #N/A. Có nghĩa là thương hiệu Jam ko mãi mãi trong những ô B3:C9.

Vì vậy, Để cách xử trí lỗi này, ta tiến hành bằng phương pháp lồng hàm VLOOKUP cùng ISNA vào bên 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 tác dụng trả về nlỗi hình dưới

*

Hình : Hàm VLOOKUPhường kết phù hợp với IF để xử lý lỗi

Cách chế biến này vẫn trả lại thương hiệu không tìm thấy. Sử dụng hàm ISNA sẽ giúp đỡ đánh giá coi công dụng của VLOOKUP gồm cần bị lỗi #N/A hay là không với triển khai ĐK IF tương ứng. Quý khách hàng có thể đặt lời nhắn văn bản không giống hoặc thậm chí còn một 0 hoặc trống ( “” ) nhỏng cổng output.

ví dụ như 3: Dùng IF để tra cứu vãn hai giá bán trị trong hàm VLOOKUP

Xét ví dụ, các ô B2:B7 đựng cực hiếm cho những sản phẩm làm việc nhị sản phẩm khác biệt.

Để search cực hiếm của sản phẩm vào ô F3, chúng ta tiến hành như sau:

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

*

Hình : Hàm VLOOKUPhường kết hợp với IF để tra cứu vãn hai giá chỉ trị

lúc thực hiện phương pháp này đã trả lại $ 2000. Hàm IF đánh giá xem quý giá vào ô G3 là Cửa hàng 1 tuyệt 2. Theo ĐK này, VLOOKUP tiếp nối trả về giá chỉ tương xứng cho sản phẩm.

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

a) HÀM LEFT : Hàm bóc tách ký từ bỏ tự phía phía trái với con số ký từ bỏ lôi ra vày mình phép tắc.

Cú pháp: 

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

*

Hình : lấy ví dụ Hàm VLOOKUP và Hàm LEFT

Chúng ta đang sử dụng hàm LEFT để 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 trên là giảm 2 ký kết tự nghỉ ngơi ô B4

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

*

Hình :Hàm LEFT trong 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 làm tìm kiếm tìm, ,$G$4:$H$8 là Vùng tài liệu tra cứu, là thiết bị trường đoản cú cột phải mang quý hiếm vào Vùng tài liệu tra cứu vớt, FALSE là phạm vi tra cứu kiếm mang ý nghĩa 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

Cách 3: Nhưng làm cho điều này sẽ tương đối mất thời hạn, họ đang tiến hành kết vừa lòng 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) và thừa nhận Enter.

Nghĩa là bọn họ sẽ rứa giá trị dùng để search kiếm $C4 thành LEFT(B4,2).

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

*

Hình : Hàm VLOOKUPhường phối kết hợp Hàm LEFT

Cách 4: Các các bạn kéo trường đoản cú ô C4, D4, E4 xuống để các ô bên dưới tự động điền công thức và đã tạo ra công dụng tương xứng.

*

Hình : Hàm VLOOKUP.. phối 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, bắt nguồn từ vị trí bởi vì người mua chỉ thị, dựa vào số lượng ký tự bởi vì khách hàng sai khiến.

Cú pháp: 

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

Cú pháp: 

= RIGHT(text, num_chars)Text: đoạn vnạp năng lượng bản đề nghị tách ký kết tựNum_chars: số ký trường đoản cú muốn tách tích từ bên đề nghị. Nếu không nhập thì đã từ bỏ nhấn quý giá là 1

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

Hàm SUMIF vào Excel thì tương tự như cùng với hàm SUM về mặt tính tổng các quý giá. Điểm khác biệt đó là hàm SUMIF tính tổng chỉ các cực hiếm đáp ứng nhu cầu tiêu chuẩn mà chúng ta đã định rõ.

Và hiện nay, nhằm đọc chúng ta xuất xắc cho với ví dụ tức thì đây. Giả sử, các bạn bao gồm Table 1: thương hiệu tín đồ bán với số ID (bảng đề xuất tìm). Và Table 2 gồm thuộc số ID cùng những con số liên quan đến doanh số . Nhiệm vụ của khách hàng là search tổng doanh thu của một người khẳng định ngay số ID của họ. Và, bao gồm nhì yếu tố phức tạp:

Bảng Table 2 đựng nhiều mục bao gồm thuộc số ID theo thiết bị trường đoản cú tình cờ.Quý khách hàng quan yếu thêm cột “Tên fan bán” vào bảng Table 2.

*

Hình: Vlookup phối kết hợp SUMIF vào Excel

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

CÔNG THỨC:

=SUMIF(E:E,VLOOKUP(I2,B4:C10,2,FALSE),F:F)SumiF với Vlookup là thương hiệu hàm tính tổng với hàm search kiếm theo điều kiện.E:E là vùng được sàng lọc nhằm chứa những ô điều kiệnI2 là cực hiếm đối chiếu cùng với cột doanh số, là quý giá dò tra cứu. Tại phía trên khi chúng ta biến đổi tên thì cột doanh số cũng biến đổi theo.B2:C10 là vật dụng trường đoản cú cột phải đem dự liệu nhằm dò gì cho quý hiếm I2 ở bên trên.Số 2 là vật dụng từ bỏ xuất quý giá, hiển thị lên screen phụ thuộc vào cột yêu cầu lấy tài liệu tất cả mấy cột, do cột Mã ID tại đoạn thứ hai đề xuất bọn họ đặt là 2.False là phạm vi tìm kiếm tìm hoàn hảo nhất mang lại tác dụng đúng đắn vắt vì chưng sử dụng True đến hiệu quả kha khá.

*

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

2.4 HÀM VLOOKUPhường. LỒNG HLOOKUPhường. DÒ TÌM DỮ LIỆU

Chúng ta tất cả ví như sau:

Tính ThanhTien ( biết cách làm ThanhTien = SoLuong * Gia ), với SoLuong vào bảng 1 và Gia trong bảng 3

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

*

Hình: Sử dụng Hàm VLOOKUP cùng HLOOKUPhường

Quý khách hàng rất có thể có tác dụng điều này dễ ợt rộng cùng với bài toán tạo cột phú. Tuy nhiên, vào trường phù hợp này ta đang thực hiện phối hợp hàm VLOOKUPhường với HLOOKUP. Cụ thể ta bao gồm phương pháp trên ô E5 như sau:

=D5*VLOOKUP(HLOOKUP(B5,$C$11:$F$12,2,FALSE),$B$15:$D$18,3,FALSE)Quý Khách rất có thể hiểu phương pháp trên nlỗi sau: Giá trị trả về của hàm HLOOKUPhường là cực hiếm 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à những ví dụ hay chạm mặt duy nhất trong việc phối hợp các hàm với hàm VLOOKUPhường. Các bạn hãy ghi ghi nhớ nhằm sử dụng tương xứng trong từng hoàn cảnh.

Xem thêm: Cách Cấp Quyền Admin Cho User Win 7, Cách Cấp Quyền Admin Cho User Trong Win 10

Để có thể sử dụng xuất sắc việc phối hợp các hàm với nhau, bọn họ phải cố rõ được xúc tích của vấn đề trước, sau kia new xác định thực hiện hàm nào, đặt sản phẩm công nghệ từ các hàm tại địa điểm làm sao.

Để rất có thể vận dụng xuất sắc Excel vào trong quá trình, họ không chỉ có nắm vững được những hàm bên cạnh đó cần sử dụng xuất sắc cả các khí cụ của Excel. Hình như còn rất nhiều hàm nâng cấp khác như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Công thế nlỗi Data validation, Pivot table, Power Query…

GÓC HỌC TẬP

THAM KHẢO THÊM