Cách Sử Dụng Vlookup Và Hlookup

Cách sử dụng hàm Vlookup kết hợp vào Excel tương đối phong phú, linh hoạt. Trên thực tế, Lúc các từng trải quá trình phức hợp đòi hỏi buộc phải tùy biến chuyển, kết hợp các hàm với nhau thì mới giải quyết được thưởng thức. Vậy bây giờ thuộc appmobiles.info đọc bí quyết phối hợp hàm Vlookup!!

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

Hàm VLOOKUP (Tra cứu giúp dọc) search kiếm một quý giá vào cột ngoại trừ thuộc phía trái của bảng cùng tiếp nối trả về một giá trị trong và một sản phẩm xuất phát từ một cột khác mà các bạn hướng dẫn và chỉ định.

Bạn đang xem: Cách sử dụng vlookup và hlookup

Cú pháp: 

2. HÀM VLOOKUPhường 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 nhằm đối chiếu quý hiếm vào hàm VLOOKUP

Mô tả: Dựa vào cột dữ liệu bao gồm trong số ô B3: C9, hãy khám phá xem tên James được nói trong ô E3 tất cả phần thưởng trọn dựa vào doanh thu to hơn 10000$ tốt không?

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

Bước 1: Kích chuột vào tác động ô F3Cách 2: Áp dụng vào hàm ta nhận ra phương pháp tính: =IF(VLOOKUP(E3,B3:C9,2,FALSE)>10000,“Yes”,“No”)Bước 3: Nhấn Enter được hiệu quả trả về nlỗi 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

lấy một ví dụ 2: Dùng IF nhằm cách xử trí lỗi quý giá trong 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 giá trị Jam mang lại ô E3. Bước 2: Để tìm kiếm lợi nhuận, hãy gán công thức: =VLOOKUP(E3,B3:C9,2,FALSE) mang đến ô F3.

lúc tiến hành xong xuôi vẫn trả về lỗi #N/A. có nghĩa là tên Jam ko trường thọ trong số ô B3:C9.

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

Cách 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 công dụng trả về nhỏng hình dưới

*

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

Các bước 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 bình chọn coi kết quả của VLOOKUP có buộc phải bị lỗi #N/A hay là không và tiến hành ĐK IF khớp ứng. quý khách hàng hoàn toàn có thể đặt tin nhắn vnạp năng lượng bản không giống hoặc thậm chí một 0 hoặc trống ( “” ) nhỏng đầu ra output.

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

Xét ví dụ, các ô B2:B7 đựng giá trị cho các sản phẩm làm việc nhị mặt hàng không giống nhau.

Để kiếm tìm quý hiếm của thành phầm vào ô F3, các bạn tiến hành nlỗi sau:

Bước 1: Kích chuột vào ô H3Cách 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 như hình dưới

*

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

lúc tiến hành biện pháp này đang trả lại $ 2000. Hàm IF đánh giá xem giá trị trong ô G3 là Cơ sở 1 giỏi 2. Theo điều kiện này, VLOOKUP kế tiếp trả về giá bán tương xứng mang lại thành phầm.

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

a) HÀM LEFT : Hàm bóc ký kết từ từ bỏ phía phía bên trái cùng với con số ký từ mang ra vị bản thân pháp luật.

Cú pháp: 

= LEFT(text, num_chars)Text: đoạn vnạp năng lượng bạn dạng buộc phải tách ký kết tựNum_chars: số cam kết từ bỏ mong tách tích từ bỏ phía trái. Nếu ko nhập thì đã từ thừa 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 dữ liệu như hình dưới

*

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

Chúng ta vẫn sử dụng hàm LEFT nhằm lấy Mã ngành trường đoản cú cột Lớp.

Bước 1: Điền vào ô C4 công thức: =LEFT(B4,2). Ý nghĩa của phương pháp trên là cắt 2 ký từ bỏ sống ô B4

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

*

Hình :Hàm LEFT trong Excel

Bước 2: Các 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 kiếm tìm kiếm, ,$G$4:$H$8 là Vùng dữ liệu tra cứu vãn, là sản phẩm công nghệ từ cột nên đem quý giá trong Vùng tài liệu tra cứu vãn, FALSE là phạm vi tìm kiếm kiếm mang tính chất tuyệt đối hoàn hảo.

Sau kia ấn Enter. Kết trái 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 cùng với Hàm VLOOKUP

Cách 3: Nhưng có tác dụng những điều đó sẽ khá mất thời hạn, chúng ta đã tiến hành kết thích hợp 2 hàm là Vlookup cùng hàm Left với 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à chúng ta vẫn nỗ lực giá trị dùng để kiếm tìm tìm $C4 thành LEFT(B4,2).

Sau đó ấn Enter. Kết trái hiển thị là Tên ngành khớp ứng với Mã Ngành.

Xem thêm:

*

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 nhằm những ô bên dưới auto điền phương pháp cùng tạo ra tác dụng tương ứng.

*

Hình : Hàm VLOOKUPhường phối 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 vnạp năng lượng bản, bắt nguồn từ vị trí vì người dùng chỉ thị, dựa vào số lượng ký tự vì chưng bạn chỉ thị.

Cú pháp: 

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

Cú pháp: 

= RIGHT(text, num_chars)Text: đoạn vnạp năng lượng bản đề xuất tách cam kết tựNum_chars: số cam kết trường đoản cú mong muốn bóc tách tích tự bên yêu cầu. Nếu ko nhập thì sẽ từ bỏ nhấn cực hiếm là 1

2.3 SỬ DỤNG HÀM VLOOKUPhường VỚI HÀM SUMIF, SUM

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

Và bây chừ, nhằm gọi họ giỏi cho cùng với ví dụ ngay lập tức trên đây. Giả sử, bạn tất cả Table 1: thương hiệu người bán với số ID (bảng cần tìm). Và Table 2 tất cả thuộc số ID với các con số tương quan mang lại doanh thu . Nhiệm vụ của bạn là tìm tổng doanh số của một fan khẳng định ngay số ID của mình. Và, gồm hai nhân tố phức tạp:

Bảng Table 2 chứa đựng nhiều mục gồm cùng số ID theo thứ tự bỗng dưng.Quý Khách cần yếu thêm cột “Tên fan bán” vào bảng Table 2.

*

Hình: Vlookup phối hợp SUMIF trong Excel

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

*

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

2.4 HÀM VLOOKUP. LỒNG HLOOKUPhường 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 ), cùng với SoLuong trong bảng 1 cùng Gia vào bảng 3

Ý tưởng: Để giải quyết thử khám phá đó: Ta đã buộc phải 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), tiếp đến lại cần sử dụng TenHang từ hiệu quả trả về của hàm HLOOKUPhường bên trên dò kiếm tìm vào bảng 3 nhằm lấy Gia (hàm VLOOKUP) với sau cùng là nhân cùng với SoLuong nhằm ra ThanhTien.

*

Hình: Sử dụng Hàm VLOOKUPhường. với HLOOKUPhường

Bạn rất có thể có tác dụng điều ấy tiện lợi hơn cùng với vấn đề sinh sản cột phú. Tuy nhiên, vào trường phù hợp này ta đã áp dụng phối hợp hàm VLOOKUP.. cùng HLOOKUPhường. Cụ thể ta có cách làm trên ô E5 nlỗi sau:

=D5*VLOOKUP(HLOOKUP(B5,$C$11:$F$12,2,FALSE),$B$15:$D$18,3,FALSE)Quý khách hàng hoàn toàn có thể gọi bí quyết bên trên như sau: Giá trị trả về của hàm HLOOKUPhường. là quý hiếm search tìm của hàm VLOOKUPhường.

*

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

KẾT LUẬN

Trên đây là những ví dụ thường xuyên gặp gỡ tuyệt nhất trong bài toán phối hợp các hàm với hàm VLOOKUPhường. Các các bạn hãy ghi lưu giữ nhằm áp dụng tương xứng vào từng yếu tố hoàn cảnh.

Để có thể áp dụng tốt việc kết hợp các hàm với nhau, họ đề nghị cố gắng rõ được súc tích của vấn đề trước, sau kia bắt đầu xác định thực hiện hàm làm sao, đặt đồ vật trường đoản cú những hàm trên địa điểm làm sao.

Để rất có thể áp dụng giỏi Excel vào trong các bước, họ không chỉ là nắm vững được các hàm bên cạnh đó phải thực hiện tốt cả những hình thức của Excel. Dường 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, nguồn Query…

GÓC HỌC TẬP

THAM KHẢO THÊM