Hàm VLOOKUP là một trong những hàm cơ bản được sử dụng phổ biến trong Excel, với chức năng tìm kiếm và tham chiếu dữ liệu từ các bảng khác nhau, từ đó giúp hỗ trợ trong việc tính toán. Trong bài viết này, viettiep.info sẽ giúp các bạn hiểu rõ hơn về hàm VLOOKUP trong Excel cũng như cách dùng VLOOKUP nhé.
Bạn đang xem: Cách tính tên hàng trong excel
Đọc thêm cách viết hàm trên Excel tại:Hướng dẫn cách viết các hàm trong Excel chi tiết dễ hiểu nhất
Đăng ký ngay khoá Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ
Chức năng của hàm VLOOKUP trong Excel
Hàm VLOOKUP trong Excel được sử dụng khi chúng ta cần tìm kiếm một giá trị tương ứng nào đó thông qua một giá trị dữ liệu từ một cột khác đã có sẵn trong bảng. Ví dụ: dùng hàm VLOOKUP
để tham chiếu, phân loại học sinh thành các loại Giỏi, Khá, Trung bình, Yếu … thông qua giá trị điểm trung bình [đã có một bảng hoặc một vùng khác quy định giá trị điểm cho các phân loại Giỏi, Khá, Trung bình, ...]
Việc phân loại này khá giống với khi ta sử dụng hàm IF. Tuy nhiên hàm IF trong trường hợp này có quá nhiều điều kiện để xét nên sẽ rất rắc rối. Việc áp dụng cách dùngVLOOKUPsẽ giúp bạn đơn giản hóa hơn rất nhiều.
Để học cách dùng hàm VLOOKUP hiệu quả, chúng ta sẽ bắt đầu với bước tìm hiểu công thức hàm VLOOKUP trong Excel như sau:
=VLOOKUP[Lookup_value, Table_array, Col_index_ num, Range_lookup]
Trong đó
Lookup_value: là giá trị cần tìm khi áp dụng cách dùng VLOOKUP.Table_array: là bảng thông tin phụ chứa giá trị dò tìm và giá trị quy đổi.Col_index_num: là số thứ tự của cột lấy dữ liệu trong bảng thông tin phụ cần dò tìm.Range_lookup: Là giá trị Logic [TRUE=1, FALSE=0] quyết định số chính xác hay số tương đối với bảng giới hạn.
Phân biệt hàm VLOOKUP và hàmHLOOKUP trong Excel
Hai hàm HLOOKUP vàhàm VLOOKUP đều là các hàm có chức năng tìm kiếm dữ liệu trên trang tính. Tuy nhiên, liệu bạn có phân biệt được khi nào dùng HLOOKUP còn khi nào dùng hàmVLOOKUPtrong Excel không? Hãy để chúng mình giúp bạn làm sáng tỏ sự khác nhau cơ bản giữa cách dùng VLOOKUP và cách dùng HLOOKUP nhé.
Về cơ bản, hàm VLOOKUP được sử dụng khi chúng ta cần tìm kiếm dữ liệu ở các cột dọc của Excel. Trong khi đó, hàm HLOOKUP được sử dụng để tìm kiếm các dữ liệu ở hàng ngang. Chính vì vậy, bạn hãy dựa vào cách trình bày bảng của mình để quyết định mình cần áp dụng cách dùng hàm VLOOKUP hay hàm HLOOKUP nhé.
Cách sử dụng hàm VLOOKUP qua các ví dụ
Ví dụ 1 về hàm VLOOKUP
Yêu cầu
Tính giá thuế nhập khẩu của các mặt hàng theo từng đối tượng như bảng dưới đây:
Cách làm
Từ giá trị đối tượng, hàm sẽ nhập ô Thuế NK cho các mặt hàng dựa vào bảng QUY ĐỊNH THUẾ. Bạn chỉ cần nhập công thức ở ô G5 như sau:
=VLOOKUP[D5;$D$17:$F$20;2;1]*E5
Hàm VLOOKUPđược sử dụng để tìm kiếm ra thuế nhập khẩu tại Bảng quy định thuế. Ở đây, vì bảng quy định thuế cho dưới dạng cột nên ta dùng hàmVLOOKUP trong Excelchứ không phải HLOOKUP. Cách dùng VLOOKUP cụ thể qua các tham số như sau:
D5: Giá trị là đối tượng cần tìm ở đây là các đối tượng từ 1,2,3,4 [Cột Đối tượng].$D$17:$F$20: Bảng giới hạn dò tìm, chính là D17:F20 [Ấn F4 để Fix cố định giá trị để khi Copy công thức xuống các ô khác, giá trị này không thay đổi].2: Thứ tự cột giá trị cần lấy.1: Trường hợp này, ta lấy giá trị tương đối nên chọn là 1.*E5: Chính là đơn giá sản phẩm đó.
Ví dụ 2 về hàm VLOOKUP
Yêu cầu
1. Dựa vào MÃ HÀNG và dò tìm trong BẢNG THAM CHIẾU, điền TÊN HÀNG tương ứng.
2. Cột ĐƠN GIÁ thực hiện tương tự cột MÃ HÀNG
3. THÀNH TIỀN = SỐ LƯỢNG x ĐƠN GIÁ.
Hướng dẫn
Ta có, bảng tham chiếu được cho có dạng cột [cột MÃ HÀNG, cột TÊN HÀNG, cột ĐƠN GIÁ] nên ta thực hành theo cách dùng hàm VLOOKUP.Giá trị [từ ô B5:B10] dùng để dò tìm đối với cột đầu tiên của BẢNG THAM CHIẾU giống nhau nên giữ nguyên.Cột TÊN HÀNG là cột 2 trong BẢNG THAM CHIẾU. [Tương tự, cột ĐƠN GIÁ sẽ là cột 3].
Chúng ta sẽ sử dụng công thức hàm VLOOKUP trong Excel để điền cột TÊN HÀNG [C5] như sau:
=VLOOKUP[B5,$B$14:$D$16,2,1]
Trong đó, các tham số hàm VLOOKUP lần lượt là:
B5: Giá trị là đối tượng cần tìm ở đây là các đối tượng A, B hoặc C [Cột MÃ HÀNG].$B$14:$D$16: Bảng giới hạn dò tìm, chính là B14:D16 [Ấn F4 để Fix cố định giá trị để khi Copy công thức xuống các ô khác, giá trị này không thay đổi].2: Thứ tự cột giá trị cần lấy.1: Trường hợp này, ta lấy giá trị tương đối nên chọn là 1.
Tại cột ĐƠN GIÁ, chúng ta áp dụng cách dùng VLOOKUP bằng công thức hàm như sau:
=VLOOKUP[B5,$B$14:$D$16,3,1]
Cuối cùng, tại Cột THÀNH TIỀN chúng ta không cần dùng đến hàm VLOOKUP hay bất kỳ hàm nào mà chỉ cần điền một công thức tính toán đơn giản:
=E5*D5
Kết luận
Qua bài viết trên, chúng ta đã có thể hiểu được về chức năng của hàm VLOOKUP trong Excel, cũng như nắm được cụ thể về cú pháp của hàm và cách dùng VLOOKUP để thuận tiện cho việc tìm kiếm dữ liệu với những bảng cột dọc trong Excel. Hi vọng các bạn đã hiểu rõ hơn vềhàm VLOOKUP và có thể ứng dụng nó tốt nhất trong công việc của mình.
Xem thêm: Hướng Dẫn Cách Xóa Trang Trắng Ở Giữa Trong Word 2010, 2007 Và 2003
Để nắm được những kiến thức cơ bản nhất về Excel cũng như nâng cao kỹ năng Excel của mình hơn nữa, các bạn hãy tham gia khoá học Excel của viettiep.info để nắm được tất cả các công cụ của Excel và ứng dụng chúng một cách hiệu quả trong công việc nhé.
Skip to content
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ể.
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ĩa
Hà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.
=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.