Hướng dẫn cách sử dụng hàm Vlookup kết hợp hàm If, Left, Right

LI�N HỆ QUẢNG C�O TẠI Đ�Y : 0988 123 126

2.Cú pháp:

=VLOOKUP(Lookup_value, Table, Col_index_num, [Range_lookup])

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 – [tùy chọn] TRUE = đối sánh gần đúng (mặc định). FALSE = đối sánh chính xác

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à 1

Chú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ứ 2
• 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.

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.

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 1
• 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 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ượng
• LEFT(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ượng
• VLOOKUP(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, Tinvanphong.com đã 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!.

Tham khảo thêm các bài viết sau.

-Hàm VLOOKUP trong excel cách sử dụng và ví dụ cụ thể

–Hướng dẫn sử dụng hàm Transpose trong Excel

–Cách sử dụng hàm iserror trong excel