Bài viết này, sẽ giới thiệu đến các bạn hướng dẫn cách sử dụng hàm Vlookup kết hợp với hàm If, Left, Right trong Excel. Hàm Vlookup, được sử dụng khi muốn tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng, và 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. Đôi khi, hàm Vlookup phải kết hợp với các hàm khác như If, Left, Right để xử lý triệt để bảng tính và đưa ra kết quả mà bạn mong muốn. Hãy cùng chúng tôi tìm hiểu nội dung bài viết sau đây, thông qua các ví dụ cụ thể.
Bạn đang xem: Hàm if right trong excel
Nội dung chính
Hướng dẫn cách sử dụng hàm Vlookup kết hợp hàm IF, Left, RightI.Cấu trúc của hàm VlookupII.Hàm Vlookup kết hợp với các hàm khác
Hướng dẫn cách sử dụng hàm Vlookup kết hợp hàm IF, Left, Right
I.Cấu trúc của hàm Vlookup
1.Định nghĩaHàm VLOOKUP hay còn gọi là hàm tra cứu dọc, có chức năng tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng tính, 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.
2.Cú pháp:
=VLOOKUP(Lookup_value, Table, Col_index_num,
Trong đó:
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 –Xem ví dụ tại đây:Hàm VLOOKUP trong excel cách sử dụng và ví dụ cụ thể
II.Hàm Vlookup kết hợp với các hàm khác
1.Hàm VLOOKUP kết hợp với hàm Right
Hàm Right, là hàm lấy ký tự bên phải một chuỗi bất kỳ, khi kết hợp Hlookup và Right cũng tương tự như Hlookup kết hợp Left giúp cho việc dò tìm kết quả nhanh và chính xác.
Cú pháp hàm Right: =Right(text,n)
text: là chuỗi văn bản các bạn muốn trích xuất các ký tự.num_chars: số lượng ký tự mà các bạn muốn trích xuất.Chúng ta có bài tập như hình, yêu cầu của bài là tính ĐIỂM ƯU TIÊN như sau:
Biết rằng: ĐIỂM ƯU TIÊN: Căn cứ vào ký tự cuối của MÃ SỐ NGÀNH-ƯU TIÊN, tra trong bảng ĐIỂM ƯU TIÊN.
Tại ô H3 chúng ta nhập công thức như sau:
=VLOOKUP(VALUE(RIGHT(C3,1)),$G$9:$H$12,2,0)
Trong đó:
C3 là mã số ngành ưu tiên tại cột C, hàng 3.RIGHT(C3,1) là giá trị dùng để dò tìm, tương ứng một chữ số bên phải của Mã ưu tiên, giá trị dò tìm nằm ở hàng 2, bảng $G$9:$H$12( các bạn nhấn F4 để khóa bảng tính).Do cột Điểm ưu tiên chúng ta muốn lấy là số, vì vậy mà các bạn cần phải thêm VALUE vào trước hàm Right, công dụng của hàm VALUE là chuyển từ dạng text sang dạng số.Sau đó, các bạn kéo công thức cho các ô còn lại.
2.Hàm VLOOKUP kết hợp với hàm Left
Hàm Left, là hàm lấy ký tự bên trái một chuỗi bất kỳ, khi kết hợp Vlookup và Left giúp cho việc dò tìm kết quả nhanh và chính xác.
Cú pháp hàm Left: =Left(text,n)
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à 1Chúng ta có bảng báo cáo bán hàng như sau.
Yêu cầu: Lấy tên MẶT HÀNG căn cứ vào MÃ MH, tra cứu ở bảng ĐƠN GIÁ.
Tại ô B3, chúng ta sẽ có công thức như sau:
=VLOOKUP(LEFT(A3,2), $A$15:$D$21,2,0)
Trong đó:
LEFT(A3,1) là giá trị dùng để dò tìm, tương ứng hai chữ đầu tiên bên trên trái của mã số MH, giá trị dò tìm nằm ở dọc, bảng $A$15:$D$21 (các bạn nhấn F4 để khóa bảng). Kết quả trả về sẽ xếp đúng tên mặt hàng.2 là cột thứ 20 ở đây là tùy chọn đối sánh chính xác.Sau đó, các bạn kéo công thức cho các ô còn lại.
3.Hàm Vlookup kết hợp với hàm IF
Hàm IF là hàm logic, cho phép bạn đánh giá một điều kiện nhất định và trả về giá trị mà bạn chỉ định nếu điều kiện là TRUE và trả về một giá trị khác nếu điều kiện là FALSE.
Cú pháp hàm IF: =IF(Điều gì đó là True, thì làm gì, nếu không thì làm gì khác)
Tiếp tục với ví dụ trên, chúng ta sẽ tính cột ĐƠN GIÁ cho bảng báo cáo bán hàng.
Yêu cầu: Tính ĐƠN GIÁ: Căn cứ vào MÃ MH, tra cứu ở bảng ĐƠN GIÁ.
Tại ô C3, chúng ta sẽ có công thức:
=VLOOKUP(LEFT(A3,2),$A$15:$D$21,IF(RIGHT(A3,1)=”1″,3,4),0)
Trong công thức này, chúng ta kết hợp giữa các hàm Left, Right và If để lấy đơn giá cho từng mặt hàng.
Trong đó:
LEFT(A3,1) là giá trị dùng để dò tìm, tương ứng hai chữ đầu tiên bên trên trái của mã số MH, giá trị dò tìm nằm ở hàng dọc, bảng $A$15:$D$21 (các bạn nhấn F4 để khóa bảng).IF(RIGHT(A3,1)=”1″,3,4),0): Nếu như mặt hàng có ký tự đầu tiên bên phải = 1, thì nó sẽ trả về kết quả đơn giá ở cột số 3(cột C). Ngược lại, nếu như mặt hàng có ký tự đầu tiên bên phải = một số khác, mà không phải là 2 thì nó sẽ trả về kết quả ở cột 4,IF(RIGHT(A3,1)=”1″,3,4),0): 0 ở đây là tùy chọn đối sánh chính xác.4.Bài tập kết hợp Vlookup với If, Left và Right
Thêm 1 ví dụ về hàm Vlookup kết hợp với các hàm Left, Right, If cho bài tập sau đây:
Chúng ta, có 1 bảng tính BÁO CÁO BÁN HÀNG THÉP XÂY DỰNG như hình bên dưới.
Xem thêm: Nơi Bán Thay Màn Hình Iphone 6 Plus Giá Màn Hình 6 Plus Giá Bao Nhiêu Tiền ?
Yêu cầu:
1- Tên hàng: Dựa vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1.
2- Mã QG: Dựa vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1.
3- Trị giá = Số lượng * Đơn giá. Biết rằng: Đơn giá: Dựa vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1, kết hợp với Loại hàng để lấy giá trị hợp lý.
4- Phí vận chuyển = Số lượng * Giá vận chuyển. Biết rằng: Giá vận chuyển: dựa vào Mã QG, tra trong BẢNG 2. 5- Thành tiền = Trị giá + Phí vận chuyển. Nếu bán ra trong tháng 5 thì giảm 5% Thành tiền.
Chúng ta thực hiện như sau.
Câu 1: Để lấy Tên hàng, các bạn đặt công thức tại ô B4
=VLOOKUP(LEFT(A4,4),$A$12:$F$16,2,0)
Trong đó:
LEFT(A4,4) là giá trị dùng để dò tìm, tương ứng với bốn chữ đầu bên trên trái của mã hàng, giá trị dò tìm nằm ở hàng dọc, bảng $A$12:$F$16(các bạn nhấn F4 để khóa bảng).2 nghĩa là cột thứ 2 của bảng 10 ở đây là tùy chọn đối sánh chính xác.Sau đó, các bạn kéo công thức cho các ô còn lại.
Câu 2: Tính Mã QG
Tại ô C3, chúng ta sẽ có công thức:
=VLOOKUP(LEFT(A4,4),$A$12:$F$16,4,0)
Tương tự như cách giải thích ở trên, nhưng ở đây chúng ta lấy mã QG ở cột số 4, nên các bạn phải chọn 4.
Sau đó, các bạn kéo công thức cho các ô còn lại.
Câu 3: Tính cột Trị giá:
Trị giá = Số lượng * Đơn giá. Biết rằng: Đơn giá: Dựa vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1, kết hợp với Loại hàng để lấy giá trị hợp lý.
Tại ô F4, chúng ta đặt công thức.
=D4*VLOOKUP(LEFT(A4,4),$A$12:$F$16, IF(RIGHT(A4,1)=”C”,5,6),0)
Trong đó:
D4 là số lượngLEFT(A4,4) là giá trị dùng để dò tìm, tương ứng bốn chữ đầu bên trên trái của mã hàng, giá trị dò tìm nằm ở hàng dọc, bảng $A$12:$F$16 (các bạn nhấn F4 để khóa bảng).IF(RIGHT(A4,1)=”C”,5,6),0): Nếu như mặt hàng có ký tự đầu tiên bên phải là “C”, thì nó sẽ trả về kết quả đơn giá ở cột số 5(cột E). Ngược lại, nếu như mặt hàng có ký tự đầu tiên bên phải = một chữ cái khác, mà không phải là “C” thì nó sẽ trả về kết quả ở cột 6,IF(RIGHT(A3,1)=”1″,3,4),0): 0 ở đây là tùy chọn đối sánh chính xác.Sau đó, các bạn kéo công thức cho các ô còn lại.
Câu 4: Tính Phí vận chuyển
Phí vận chuyển = Số lượng * Giá vận chuyển. Biết rằng: Giá vận chuyển: dựa vào Mã QG, tra trong BẢNG
Tại ô G4, chúng ta đặt công thức
=D4*VLOOKUP(C4,$H$13:$J$15,3,0)
Trong đó:
D4 là số lượngVLOOKUP(C4,$H$13:$J$15,3,0) là giá trị dùng để dò tìm mã QG, giá trị dò tìm nằm ở hàng dọc, bảng $H$13:$J$15 (các bạn nhấn F4 để khóa bảng), 3 ở đây là giá trị cần lấy nằm ở cột số 3.0 là tùy chọn đối sánh chính xác.Câu 5: Tính cột thành tiền, biết rằng : Thành tiền = Trị giá + Phí vận chuyển. Nếu bán ra trong tháng 5 thì giảm 5% Thành tiền.
Tại ô H4, chúng ta đặt công thức
=IF(MONTH(E4)=5,(F4+G4)*5%,F4+G4)
Trong đó:
IF(MONTH(E4)=5,(F4+G4)*5%,F4+G4): Nếu E4 =5, thì (trị giá + phí vận chuyển)*5%, ngượi lại không phải tháng 5 thì trị giá + phí vận chuyển.
Như vậy, pgdngochoi.edu.vn đã hướng dẫn các bạn cách sử dụng hàm Vlookup kết hợp hàm If, Left, Right thông qua các ví dụ. Hy vọng, qua các ví dụ này, sẽ giúp các bạn hiểu rõ hơn khi nào chúng ta sẽ cần kết hợp hàm Vlookup với các hàm khác, để có được kết qảu như mong muốn. Chúc các bạn thành công!.
