Hàm VLOOKUP là một trong các hàm Excel hữu ích nhất tuy nhiên lại ít người tìm hiểu về nó. Trong bài viết này, chúng tôi sẽ giúp cho các bạn hiểu rõ về hàm VLOOKUP trong Excel qua các ví dụ cụ thể.
Trong bài viết thì này chúng ta hãy cùng tìm hiểu về các công thức và cách dùng hàm Vlookup nâng cao, cách sử dụng hàm VLookup lồng các hàm khác như hàm IF trong Excel để có thể tìm kiếm theo nhiều điều kiện và cách tra cứu hai chiều với hàm VLOOKUP. Hãy cùng 123job tìm hiểu nhé!
I. Làm thế nào sử dụng Vlookup để lấy 2.3.4,... giá trị
Hàm Vlookup trong Excel chỉ có thể lấy một số giá trị phù hợp, chính xác hơn, chính là giá trị đầu tiên được bạn tìm thấy. Thế nếu có nhiều giá trị để bạn tra cứu và bạn muốn xuất hiện ở hai ba lần? Hay nhiều hơn nữa thì nếu bạn muốn kéo tất cả các giá trị mang tính phù hợp? Nghe có vẻ thực hiện rất phức tạp, tuy nhiên vẫn có cách thực hiện.
Cách sử dụng hàm Vlookup trong Excel để lấy 2.3.4,... giá trị
Chẳng hạn bạn có tên khách hàng trong một cột và các sản phẩm của họ mua ở một cột khác. Và hiện tại, bạn muốn tìm sản phẩm thứ 2 thứ 3 hay thứ 4 do cùng một khách hàng mua.
Cách đơn giản nhất chính là thêm cột phụ trước cột Customer Names và điền tên sau đó thêm số thứ tự vào, chẳng hạn như là “John Doe 1”, “John Doe 2” ...Công thức COUNTIF dưới đây sẽ là mẹo nhỏ để thực hiện việc này (giả sử tên khách hàng Nằm trong cột B): =B2&COUNTIF($B$2:B2,B2)
Sau đó, bạn có thể sử dụng hàm VLOOKUP ttheo công thức thông thường để tìm thứ tự tương ứng. Ví dụ:
- Tìm sản phẩm thứ hai do chính Dan Brown mua: =VLOOKUP(“Dan Brown 2”,$A$2:$C$16,3,FALSE)
- Tìm sản phẩm thứ ba do chính Dan Brown mua: =VLOOKUP(“Dan Brown 3”,$A$2:$C$16,3,FALSE)
Đương nhiên thì bạn có thể nhập một tham chiếu ô thay vì các văn bản trong giá trị bạn đã tra cứu. Nếu bạn đang tìm kiếm lần xuất hiện thứ 2 thì bạn có thể làm mà không cần tạo ra các cột phụ bằng cách tạo ra một công thức VLOOKUP phức tạp hơn:
=IFERROR(VLOOKUP($F$2,INDIRECT(“$B$”&(MATCH($F$2,Table4[Customer Name],0)+2)&”:$C16″),2,FALSE),””)
Trong công thức sử dụng hàm VLOOKUP bao gồm:
- $F$2 – ô có tên khách hàng ( chính là hằng số và lưu ý các tham chiếu ô tuyệt đối);
- $B$ – cột “Customer Names”;
- Table 4 [Customer Name] – tra cứu các cột trong bảng hay một phạm vi tra cứu;
- $C16 – ô cuối cùng (ở dưới cùng bên trái) trong bảng tra cứu hàm của bạn.
II. Làm thế nào để làm tra cứu hai chiều trong Excel
Hãy tìm kiếm 2 chiều trong Excel (còn được gọi là tra cứu ma trận hay tra cứu hai chiều) tìm kiếm trên các hàng và cột. Nói theo cách khác, bạn tìm kiếm một giá trị tại giao điểm của một hàng và cột nhất định.
Chính vì vậy, hãy sử dụng hàm Vlookup trong bảng “Monthly Sales” một lần nữa và viết công thức hàm VLOOKUP để tìm xem bao nhiêu lemons được bán vào tháng 3.
III. Các dải được đặt tên & SPACE (toán tử giao nhau)
Nếu bạn không thích những công thức các hàm trong Excel phức tạp thì bạn có thể sử dụng cách dễ nhớ này:
1. Hãy chọn bảng của bạn sau đó chuyển sang tab Formulas và nhấp vào Create from Selection.
2. Microsoft Excel cũng sẽ tạo ra các tên từ các giá trị trong hàng trên cùng và các cột bên trái với lựa chọn của bạn, và bạn sẽ có thể tìm kiếm các tên đó trực tiếp thay vì tạo thành một số công thức .
3. Trong bất kỳ trong ô rỗng nào khi gõ =row_value column_value, ví dụ =Lemons Mar, hay ngược lại = Mar Lemons.
Hãy nhớ tách các giá trị hàng và giá trị cột của bạn cùng với khoảng trắng, trong trường hợp này thì chúng sẽ hoạt động như một toán tử giao nhau. Khi bạn gõ, Microsoft Excel thì chúng sẽ hiển thị một danh sách các tên phù hợp, chính xác như khi bạn bắt đầu gõ một công thức
4. Ấn phím Enter sau đó xem lại kết quả.
Tất cả các cách, tùy theo phương pháp bạn chọn, kết quả tra cứu hai chiều cũng sẽ giống nhau
IV. Làm thế nào để làm nhiều Vlookups trong một công thức (Vlookup lồng nhau)
Đôi khi tạo bảng chính và bảng tra cứu của bạn sẽ không có một cột chung nào và điều này không cho bạn thực hiện một cách dùng hàm Vlookup trong bình thường. Nhưng, có một số bảng khác và không chứa thông tin bạn đang tìm kiếm tuy nhiên có một cột chung với bảng chính của bạn và một chung cùng với bảng tra cứu của bạn.
Hãy xem xét ví dụ dưới đây. Bạn có bảng chính với một cột đơn là New SKU, và bạn cần phải kéo giá tương ứng từ một bảng khác. Ngoài ra thì bạn có 2 bảng tra cứu – mã đầu tiên chứa mã số New SKU và tên sản phẩm tương tự, cái thứ hai liệt kê tên sản phẩm, giá cả, tuy nhiên có Mã SKU old.
Để kéo giá từ bảng Lookup 2 đến bảng Chính, bạn phải thực hiện cái gọi là vlookup đôi của Excel, hoặc vlookup lồng nhau. Tạo công thức hàm VLOOKUP tìm tên sản phẩm trong ” Lookup table 1“, sử dụng SKU New làm giá trị tra cứu: =VLOOKUP(A2,New_SKU,2,FALSE)
Trường hợp ‘New_SKU’ là phạm vi được đặt tên cho $A:$B trong ” Lookup table 1 “ và ” Lookup table 2″ , thì cột B có chứa tên sản phẩm .
Viết các công thức để kéo giá từ ” Lookup table 2 “, dựa vào tên của sản phẩm bằng cách kết hợp hai hàm vlookup trong excel ở trên trong tiêu chí tra cứu: =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)
Trong đó Price là một phạm vi được đặt tên $A:$C trong bảng tra cứu 2, và 3 là cột C chứa giá.
V. Làm thế nào để có được tất cả các giá trị trùng lặp trong phạm vi tra cứu
Hướng dẫn để có được tất cả các giá trị trùng lặp trong phạm vi tra cứu
Như đã đề cập ở trên, cách dùng hàm VLOOKUP trong excel không thể nhận được các bản sao trùng lặp của giá trị tra cứu. Để thực hiện điều này, bạn sẽ cần một công thức mảng phức tạp hơn bao gồm các hàm Excel như là: INDEX, SMALL và ROW.
Chẳng hạn trong công thức dưới đây tìm tất cả các trường hợp của giá trị trong ô F2 trong phạm vi tra cứu B2: B16 sau đó trả về giá trị từ cột C trong các hàng tương tự:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,””), ROW()-3)),””)}
Sao chép các công thức dưới đây vào một vài ô lân cận, ví dụ như là: các ô F4:F8 . Số lượng ô mà bạn sao chép công thức phải bằng hay lớn hơn số mục trùng lặp tối đa. Ngoài ra, nhớ nhấn Ctrl + Shift + Enter để nhập một công thức mảng một cách chính xác.
Nếu bạn tò mò muốn biết logic của công thức cách dùng hàm Vlookup, chúng ta hãy cùng tìm hiểu sâu một chút:
Phần 1 .IF($F$2=B2:B16, ROW(C2:C16)-1,””)
$F$2=B2:B16– so sánh hai giá trị trong ô F2 cùng với mỗi giá trị trong dải B2: B16. Nếu tìm thấy giá trị phù hợp, ROW(C2:C16)-1 trả lại số của hàng tương ứng (-1 được sử dụng để khấu trừ dòng tiêu đề). Nếu các giá trị so sánh không khớp, hàm IF trong excel trả về một chuỗi rỗng.
Kết quả của hàm IF trong excel là mảng sau đây: {1, “”, 3, “”, 5, “”, “”, “”, “”, “”, “”,12, “”, “”, “”}
Phần 2 .ROW()-3
Trong trường hợp này, hàm ROW trong phần mềm Excel hoạt động như là một bộ đếm thêm. Vì công thức được sao chép vào trong ô F4: F9, ta thêm -3 cho hàm trả 1 cho ô F4 (hàng 4 trừ 3), 2 cho ô F5 (hàng 5 trừ 3), vv
Phần 3 .SMALL(IF($F$2=$B$2:$B$16,ROW($C$2:$C$16)-1,””),ROW()-3))
Hàm SMALL trong excel sẽ trả về giá trị nhỏ nhất thứ k trong một bộ dữ liệu. Trong trường hợp của này ở vị trí (từ nhỏ nhất) trở về sẽ được xác định bởi hàm ROW trong Excel. Vì vậy, đối với ô F4, hàm SMALL ({array}, 1) trả về phần tử trước tiên (nhỏ nhất) của mảng, tức là 1. Đối với ô F5, nó sẽ trả về phần tử nhỏ thứ 2 của mảng, là 3, trên.
Phần 4 .INDEX($C$2:$C$16, SMALL(IF($F$2=$B$2:$B$16, ROW($C$2:$C$16)-1,””), ROW()-3))
Hàm INDEX trong excel chỉ đơn giản trả về giá trị của một ô xác định trong mảng C2: C16. Đối với ô F4, INDEX ($C$2:$C$16,1) trả về “Apples”; Cho tế bào F5, INDEX ($C$2:$C$16,3) trả về “Sweets”, v.v.
Phần 5 .IFERROR()
Cuối cùng, chúng ta hãy lồng công thức trong hàm IF trong excel bởi vì chắc bạn không muốn thấy thông báo N / A trong bảng tính của bạn và khi số ô mà bạn đã sao chép với công thức hàm IF trong Excel lớn hơn số lần xuất hiện trùng lặp của giá trị tra cứu.
VI. Cách sử dụng kết hợp hàm Vlookup với hàm Left
Hàm VLOOKUP trong Excel: Là hàm tra cứu và được trả về kết quả theo hàng dọc.
Theo cú pháp hàm Vlookup: =Vlookup(lookup_value, table_array, col_index_num, [range_lookup]
Trong đó:
- Lookup_value: Giá trị dùng để dò tìm
- Table_array: Vùng dữ liệu tra cứu
- Col_index_num: Thứ tự của cột bạn cần lấy dữ liệu trong bảng giá trị dò.
- Range_lookup: Phạm vi tìm kiếm là TRUE tương đương với 1 (dò tìm tương đối), FALSE là tương đương với 0 (dò tìm tuyệt đối).
Lưu Ý: Đối với giá trị lookup_value hãy ấn F4 3 lần còn đối với giá trị table_array hãy ấn F4 1 lần.
Ý NGHĨA CỦA VIỆC SỬ DỤNG F4
- F4 (1 lần): để có giá trị tuyệt đối. Tuyệt đối được hiểu là cố định cột và cố định dòng ⇒ $cột$dòng
Ví dụ: $A$8 ⇒ hãy cố định cột A và cố định dòng 8
- F4 (2 lần): để có giá trị tương đối cột và tuyệt đối dòng - Được hiểu là cố định dòng , không cố định cột ⇒ cột$dòng
Ví dụ: A$8 ⇒ cố định dòng 8 sau đó không cố định cột A
- F4 (3 lần): để có giá trị tương đối dòng và tuyệt đối cột - Được hiểu là cố định cột, không cố định dòng ⇒ $ cột dòng
HÀM LEFT: Là hàm dùng để cắt lấy các ký tự bên trái của một chuỗi ký tự
- Cú Pháp: LEFT(text,n)
Cách sử dụng kết hợp hàm Vlookup với hàm Left
TRONG ĐÓ:
- Text: chuỗi ký tự cần cắt
- N: số ký tự muốn cắt
- Bước 1: Hãy điền vào ô C6 trong công thức: =LEFT(B6,2). Ý nghĩa của công thức trên là cắt 2 ký tự ở ô B6
- Bước 2: Sau đó ấn Enter. Kết quả hiện ra sẽ là 2 ký tự CN đã được cắt ra từ các chuỗi ký tự CNTT1.
- Bước 3: Các bạn kéo từ ô C6 xuống để các ô bên dưới tự động điền các công thức và cho ra kết quả tương ứng.
- Bước 4: Các bạn điền vào ô D6 công thức hàm vlookup: =VLOOKUP($C6,$G$6:$H$10,2,0). $C6 là Giá trị dùng để tìm kiếm, $G$6:$H$10 là Vùng dữ liệu tra cứu, 2 là thứ tự các cột bạn cần lấy giá trị trong Vùng dữ liệu tra cứu, 0 chính là phạm vi tìm kiếm mang tính tuyệt đối.
- Bước 5: Sau đó ấn Enter. Kết quả hiện ra chính là Tên ngành tương ứng với Mã Ngành. Các bạn lại kéo xuống để các ô bên dưới tự động điền công thức và cho ra kết quả tương ứng
- Bước 6: Các bạn điền vào ô E6 công thức hàm vlookup: =VLOOKUP(LEFT(B6,2),$G$6:$H$10,2,0). Chúng ta hãy thay các giá trị về cách dùng hàm Vlookup để tìm kiếm từ $C6 thành LEFT(B6,2).
- Bước 7: Sau đó ấn nút Enter và làm tương tự như ở bước 5.
VII. Kết luận
Trên đây là hướng dẫn cách sử dụng hàm Vlookup và những thông tin bổ ích liên quan đến hàm Vlookup trong Excel. Hi vọng rằng sau bài viết này, các bạn có thể dễ dàng sử dụng các hàm để thuận tiện hơn trong công việc văn phòng của mình. Chúc bạn thành công
Trang chủ 
Mẫu CV 
Nhắn tin 
Tài khoản 
Thêm