Bạn đang xem bài viết Hàm Vlookup Kết Hợp If, Left, Mid, Right, Hlookup được cập nhật mới nhất trên website Utly.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
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!!
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, 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
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 ô , hãy tìm hiểu xem tên James được đề cập trong ô 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 hiện:
Bước 3: Nhấn Enter được kết quả trả về như hình phía bên dưới
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
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 ô
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 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
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 2: Trong ô H3 nhập công thức sau:
Bước 3: Ấn Enter được kết quả như hình dưới
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 dữ liệu như hình dưới
Nghĩa là chúng ta sẽ thay giá trị dùng để tìm kiếm $C4 thành LEFT(B4,2).
Cú pháp:
= MID(text, start_num, num_chars)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õ.
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.
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.
Chúng ta có ví dụ 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ô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.
Để có thể sử dụng tốt việc kết hợp các hàm với nhau, chúng ta cần nắm rõ được logic của vấn đề trước, sau đó mới xác định sử dụng hàm nào, đặt thứ tự các hàm tại vị trí nào.
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các 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…
VIDEO THỰC HÀNH MỘT SỐ TÌNH HUỐNG THỰC TIỄN :
Hàm Vlookup Kết Hợp Hàm If Trong Excel Và Hàm Hlookup + If
Hàm VLOOKUP kết hợp hàm if / Hàm hlookup trong excel với các phiên bản hoàn toàn giống nhau. Trường cũng nói về cách sử dụng hàm HLOOKUP kết hợp hàm if.
Vlookup là một hàm tìm kiếm rất phổ biến trong excel. Bài viết gồm có: Cú pháp, ví dụ minh họa, bài tập thực hành.
1/ Khi nào dùng hàm vlookup và hlookup trong excel.
Vlookup – là hàm tìm kiếm theo cột:
Hlookup – là hàm tìm kiếm theo dòng.
Điểm chung giữa 2 hàm tìm kiếm này:
– Đều là hàm tìm kiếm giá trị
– Cú pháp hàm đều gồm 4 thành tố: Điều kiện tìm kiếm, vùng tìm kiếm, cột/ dòng chỉ định và kiểu tìm kiếm.
Sự khác nhau giữa hai cú pháp hàm excel trên là ở thành tố thứ 3:
+ Cột chỉ định – áp dụng cho hàm vlookup
+ Dòng chỉ định – áp dụng cho hàm hlookup
2/ Ví dụ về minh họa vlookup kết hợp if/ hlookup kết hợp if
Tính Số Điện tiêu thụ trong và ngoài định mức – Dựa trên “Bảng Định Mức” phía dưới.
Nếu số tiêu thụ < số định mức: Số trong định mức = số tiêu thụ
Số tiêu thụ = Số mới – Số cũ
Số định mức: dùng hàm vlookup hoặc hàm hlookup để tìm từ Bảng định mức.
Nhiệm vụ tiếp theo là tìm số tiêu thụ định mức để so sánh với số tiêu thụ là chúng ta sẽ biết được số trong và ngoài định mức.
Làm thế nào để tính được Số tiêu thụ định mức của từng hộ gia đình.
Theo như Bảng Định Mức ở dưới thì Số tiêu thụ định mức của mỗi hộ được xác định dựa trên 2 yếu tố:
Cột: A, B, C, D
Dòng: NN, CB, ND, KD, SX
Ở trong Bảng Thanh Toán Tiền Điện:
Chúng ta sẽ thấy mỗi một hộ gia đình đều có Mã Hộ ở cột D, trong đó Mã hộ gồm 2 phần:
– 2 Ký tự đầu tiên: là một trong 5 loại sau: NN, CB, ND, KD, SX
– 1 Ký tự cuối cùng: là một trong 4 loại sau: A, B, C, D
Như vậy ta có thể dùng hàm Vlookup để tìm theo A, B, C, D hoặc dùng hàm Hlookup để tìm theo NN, CB, ND, KD, SX.
Trong bài này tôi sẽ hướng dẫn dùng hàm Vlookup.
Cấu trúc của hàm Vlookup ( Lookup_value, Table_array , Col_index_num, Range_lookup )
2.1/ Cách sử dụng hàm Vlookup kết hợp hàm if trong ví dụ này:
IF(LEFT(D3,2)=”NN”,2,IF(LEFT(D3,2)=”CB”,3,IF(LEFT(D3,2)=”ND”,4,
(2) – Table_array: Vùng tìm kiếm, chính là Bảng Định Mức, trong đó cột đầu tiên trong vùng tìm kiếm là cột chứa A, B, C và D
Vùng điều kiện nên được cố định cả dòng cả cột.
Vì như thế khi copy công thức sang ô khác thì vùng điều kiện không bị thay đổi.
Bởi lẽ tất cả các hộ tiêu thụ điện đều có số định mức nằm trong bảng định mức.
(3) – Col_index_num: Số thứ Cột chứa giá trị đang tìm kiếm (Cột chỉ định)
Ở trường số (3) chúng ta sẽ dễ dàng nhận thấy rằng:
Nếu 2 ký tự đầu của mã hộ là NN thì Cột chỉ định sẽ là 2 tính từ cột đầu tiên. Còn nếu không phải NN mà là CB thì cột chỉ định sẽ là 3, …
Chúng ta suy luận tương tự thì chúng ta sẽ tìm được tất cả các trường hợp cho cột chỉ định.
Ở trường này chúng ta sẽ phải dùng hàm Left để tách ra 2 ký tự đầu tiên trong Mã hộ. Sau đó dùng kết quả của hàm này để tìm số thứ tự cột chỉ định
(4) – Range_lookup: Kiểu tìm kiếm là 0 – Kiểu tìm kiếm chính xác 100%.
2.2/ Cách sử dụng hàm Hlookup kết hợp hàm if trong ví này:
Sự khác nhau giữa 2 hàm này là:
– Tên hàm: Hlookup thay vì Vlookup
– Điều kiện tìm kiếm: khi dùng hàm hlookup thì dòng đầu tiên (row 18) của vùng dữ liệu chứa 2 ký tự đầu của mã hộ NN hoặc CB. Do đó điều kiện tìm kiếm cần tách ra 2 ký tự đầu tiên của mã từng hộ dân.
– Thành tố thứ 3 là Dòng chỉ định, thay vì cột chỉ định như CỘT chỉ định.
Nhìn ở Bảng định mức:
A: là dòng thứ 2 tính từ dòng 18
B: là dòng thứ 3 tính từ dòng 18
….
Như vậy, Trường đã hướng dẫn xong các bạn về Hàm vlookup kết hợp hàm if/ Hàm hlookup kết hợp hàm if.
Tổng hợp bài tập excel cơ bản đến nâng cao
Hàm If Kết Hợp Hàm Left
Hàm IF kết hợp hàm LEFT trong excel. Khi điều kiện sử dụng trong hàm if được lấy từ một hoặc một vài ký tự của một chuỗi text tính từ bên trái.
Ví dụ như Mã phiếu chi chẳng hạn: PC01001.
PC đại diện cho Phiếu Chi
01 đại diện cho tháng phát sinh
001 – Phiếu chi số 001 của tháng.
HƯỚNG DẪN SỬ DỤNG HÀM IF KẾT HỢP HÀM LEFT
Tìm tên mặt hàng cho từng lần bán hàng căn cứ vào Mã Hóa Đơn biết rằng:
Nếu ký tự đầu tiên của Mã HĐ là J thì tên mặt hàng là Jean, còn lại nếu là K thì tên mặt hàng Kaki.
Mỗi một nghiệp vụ bán hàng đều có một Mã Hóa đơn riêng, Mã hóa đơn trong ví dụ này được cấu tạo bởi:
– Ký tự đầu tiên: là mã của từng mặt hàng
– Ký tự tiếp theo: là mã giảm giá
– 3 ký tự cuối cùng: là mã số của nghiệp vụ
Như vậy để hoàn thành yêu cầu trên chúng ta phải dùng hàm if để tìm tên mặt hàng với diễn giải hàm như sau:
= IF ( Ký tự đầu tiên của Mã HĐ = “J”, “Jean”, “Kaki”)
Hàm IF ở đây chỉ có 3 thành tố mà không cần phải lồng thêm hàm nào nữa đó là bởi vì chúng ta chỉ có hai mặt hàng theo như danh sách này là: Jean và Kaki,
Do đó khi so sánh ký tự đầu tiền của Mã HĐ với “J”, nếu nó thỏa mãn thì tên mặt hàng sẽ là Jean, còn lại nếu không thỏa mãn thì tên mặt hàng sẽ là Kaki.
Vấn đề là làm thế nào để tách được ký tự đầu tiên từ Mã HĐ:
Để làm được điều này ta sẽ sử dụng hàm LEFT.
= LEFT ( Mã HĐ, 1)
Số 1 trong hàm left có nghĩa là chúng ta chỉ muốn lấy 1 ký tự tính từ bên trái của Mã HĐ.
[sociallocker id=”515″]
Cách sử dụng hàm IF kết hợp hàm LEFT trong ví dụ này được viết như sau:
=IF (LEFT (B3,1) = “J”, “Jean”, “Kaki”)
Bạn cần nhập công thức trên vào ô đầu tiên trong cột mặt hàng, sau đó copy công thức cho các dòng còn lại trong cột mặt hàng.
Xong, như vậy là bạn đã tìm được tên của một danh sách hàng nghìn dòng mặt hàng chỉ trong nháy mắt với việc sử dụng hàm excel.
Hướng Dẫn Cách Sử Dụng Hàm Vlookup Kết Hợp Left
Khái quát hàm Vlookup và Left
– Nhấn 1 lần F4: Mục đicha là có giá trị tuyệt đối. Tuyệt đối ở đây được hiểu như là là cố định một dòng và cố định một cốt. Ký hiệu: $cột$dòng
Ví dụ cụ thể: $B$9 ⇒ Cố định cột B và dòng 9
– Nhấn 2 lần F4: Để trả về giá trị tuyệt đối dòng và tương đối cột. Như vậy, bản chất của nó được hiểu là không cố định cột nhưng cố định dòng. Ký hiệu: cột$dòng
Ví dụ: B$9 ⇒ Không cố định cột B mà cố định dòng 9
– Nhấn 3 lần F4: Nhận giá trị tuyệt đối cột và tương đối dòng. Như vậy, bản chất của nó được hiểu là không cố định dòng, cố định cột. Ký hiệu: $cộtdòng
Ví dụ: $B9 ⇒ Cố định cột B mà không cố định dòng 9.
Hàm LEFT là một hàm cắt đọc lấp các chuỗi ký tự bắt đầu từ phía bên trái trong một chuỗi văn bản. Tuy nói như thế nhưng thực tế thì hàm Left thường được kết hợp cùng với các hàm cơ bản khác để tăng hiệu quả. Cú pháp: = LEFT(text, n).
text: Đây là giá trị bắt buộc. Một chuỗi văn bản chứa các ký tự các bạn muốn tách ra.
n: Số ký tự mà bạn muốn cắt được tính từ phía bên trái chuỗi văn bản đó.
n chắc chắn phải lớn hơn bằng 0. Nếu nhỏ hơn 0 sẽ trả về cho bạn là giá trị #VALUE!
Nếu giá trị n lớn hơn tổng số ký tự ở chuỗi văn bản, thì hàm LEFT trả về kết quả là toàn bộ chuỗi văn bản đó.
Nếu như khi thực hiện mà không nhập vào giá trị n vào thì nó sẽ mặc định lấy giá trị 1
Hàm Vlookup kết hợp với hàm Left
Trong đó, hàm Vlookup kết hợp Left một sự kết hợp hoàn hảo. Khi kết hợp 2 hàm này với nhau thì hàm Vlookup có nhiệm vụ chính đảm nhiệm đó chính là tìm kiếm các thông tin mà bàn cần một cách chính xác và nhanh hơn. Còn về hàm Left thì các bạn sẽ tiến hành phân tách chuỗi văn bản để lấy phần ký tự mình cần từ phía bên trái. Chúng ta sẽ kết hợp hàm Left nằm trong phần công thức của hàm và nhấn Enter.
Hi vọng rằng những thông tin ở bài viết trên, các bạn có thể dễ dàng giải quyết bài tập tính lương hay cắt ký tự hay các phần nâng cao hơn trong Excel.
Cập nhật thông tin chi tiết về Hàm Vlookup Kết Hợp If, Left, Mid, Right, Hlookup trên website Utly.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!