Hàm Vlookup là một trong những hàm Excel hữu ích nhất, nhưng ít người tìm hiểu về nó. Trong bài viết này, chúng tôi sẽ giúp bạn hiểu rõ hơn về hàm Vlookup trong Excel thông qua các ví dụ cụ thể.
Trong bài viết này, chúng ta hãy cùng nhau tìm hiểu về công thức và cách sử 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. Cách sử dụng Vlookup để nhận giá trị 2.3.4, …
Hàm Vlookup trong Excel chỉ có thể lấy một số giá trị phù hợp, chính xác hơn là giá trị đầu tiên mà bạn tìm thấy. Điều gì sẽ xảy ra nếu có nhiều giá trị để bạn tra cứu và bạn muốn xuất hiện hai hoặc ba lần? Hoặc nhiều hơn sau đó nếu bạn muốn kéo tất cả các giá trị phù hợp thì sao? Nghe có vẻ rất phức tạp để làm, nhưng có một cách để làm điều đó.
Cách sử dụng hàm Vlookup trong Excel để nhận giá trị 2.3.4, …
Ví dụ: bạn có tên khách hàng trong một cột và sản phẩm của họ đã mua trong một cột khác. Và bây giờ, bạn muốn tìm sản phẩm thứ 2, 3 hoặc 4 được mua bởi cùng một khách hàng.
Cách đơn giản nhất là thêm một cột phụ trước cột Tên khách hàng và điền tên, sau đó thêm số sê-ri, chẳng hạn như “John Doe 1”, “John Doe 2” … Công thức COUNTIF dưới đây sẽ thực hiện thủ thuật . 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 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)
Tất nhiên, bạn có thể nhập tham chiếu ô thay vì nhập văn bản trong giá trị mà bạn đã tra cứu. Nếu bạn đang tìm kiếm lần xuất hiện thứ 2, bạn có thể làm điều đó mà không cần tạo thêm cột bằng cách tạo 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),” ”)
Công thức sử dụng hàm VLOOKUP bao gồm:
$ F $ 2 – ô có tên khách hàng (là hằng số và lưu ý các tham chiếu ô tuyệt đối);
$ B $ – cột “Tên khách hàng”;
Bảng 4 [Customer Name] – các cột tra cứu trong một bảng hoặc 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. Cách tra cứu hai chiều trong Excel
Thực hiện tìm kiếm 2 chiều trong Excel (còn được gọi là tra cứu ma trận hoặc tra cứu hai chiều) để tìm kiếm hàng và cột. Nói 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.
Do đó, hãy sử dụng lại hàm Vlookup trong bảng “Doanh số hàng tháng” và viết công thức hàm Vlookup để tìm xem có bao nhiêu quả chanh đã được bán trong tháng Ba.
III. Dải ô được đặt tên & SPACE (toán tử giao lộ)
Nếu bạn không thích các công thức và hàm phức tạp trong Excel, bạn có thể sử dụng cách dễ nhớ này:
1. Chọn bảng của bạn, sau đó chuyển sang tab Công thức và nhấp vào Tạo từ Lựa chọn.
2. Microsoft Excel cũng sẽ tạo tên từ các giá trị ở hàng trên cùng và 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 những tên đó trực tiếp thay vì tạo một số công thức.
3. Trong bất kỳ ô trống nào, hãy nhập = row_value column_value, ví dụ = Lemons Mar, hoặc ngược lại = Mar Lemons. Hãy nhớ tách các giá trị hàng và cột của bạn cùng với một khoảng trắng, trong trường hợp đó, chúng sẽ hoạt động như một toán tử giao nhau. Khi bạn nhập, Microsoft Excel sẽ hiển thị danh sách các tên trùng khớp, giống như khi bạn bắt đầu nhập công thức.
4. Nhấn phím Enter rồi xem lại kết quả. Tất cả các cách, tùy thuộc vào phương pháp bạn chọn, kết quả tra cứu hai chiều cũng sẽ giống nhau
IV. Cách thực hiện nhiều Vlookup trong một công thức (Vlookup lồng nhau)
Đôi khi việc tạo bảng chính và bảng tra cứu của bạn sẽ không có một cột nào chung và điều này không cho phép bạn sử dụng bình thường hàm Vlookup. Tuy nhiên, 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 cột chung với bảng tra cứu của bạn.
Hãy xem xét ví dụ dưới đây. Bạn có một bảng chính với một cột New SKU và bạn cần kéo giá tương ứng từ một bảng khác. Ngoài ra, bạn có 2 bảng tra cứu – bảng thứ nhất ghi số SKU mới và tên sản phẩm tương tự, bảng thứ hai liệt kê tên sản phẩm, giá nhưng có Mã SKU cũ.
Để kéo giá từ bảng Tra cứu 2 sang bảng Chính, bạn phải thực hiện cái được gọi là vlookup kép 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 “Bảng tra cứu 1”, sử dụng SKU Mới làm giá trị tra cứu: = VLOOKUP (A2, New_SKU, 2, FALSE)
Trong đó ‘New_SKU’ là phạm vi được đặt tên cho $ A: $ B trong “Bảng tra cứu 1” và “Bảng tra cứu 2”, thì cột B chứa tên sản phẩm.
Viết công thức để lấy giá từ “Bảng tra cứu 2”, dựa trên 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 ,SAI)
Trong đó Giá là 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. Cách lấy tất cả các giá trị trùng lặp trong phạm vi tra cứu
Hướng dẫn lấy tất cả các giá trị trùng lặp trong phạm vi tra cứu
Như đã đề cập ở trên, sử 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. Để làm đ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ư: INDEX, SMALL và ROW.
Ví dụ: công thức sau 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 và sau đó trả về giá trị từ cột C trong các hàng giống nhau:
{= IFERROR (INDEX ($ C $ 2: $ C $ 16, NHỎ (IF ($ F $ 2 = B2: B16, ROW (C2: C16) -1, “”), ROW () – 3)), “”) }
Sao chép các công thức bên dưới vào một số ô lân cận, ví dụ: ô F4: F8. Số ô mà bạn sao chép công thức phải bằng hoặc lớn hơn số bản sao tối đa. Ngoài ra, hãy nhớ nhấn Ctrl + Shift + Enter để nhập công thức mảng một cách chính xác.
Nếu bạn muốn biết logic của công thức cách sử dụng hàm Vlookup, hãy tìm hiểu sâu hơn 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 phạm vi B2: B16. Nếu tìm thấy kết quả phù hợp, ROW (C2: C16) -1 trả về số của hàng tương ứng (-1 được sử dụng để trừ dòng tiêu đề). Nếu các giá trị so sánh không khớp, hàm IF trong excel sẽ trả về một chuỗi trống.
Kết quả của hàm IF trong excel là mảng sau: {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ư một bộ đếm phụ. Vì công thức được sao chép vào các ô F4: F9, chúng tôi thêm -3 vào hàm trả về 1 cho ô F4 (hàng 4 trừ 3), 2 cho ô F5 (hàng 5 trừ 3), v.v.
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 tập dữ liệu. Trong trường hợp này, vị trí (từ nhỏ nhất) của lợi nhuận sẽ được xác định bởi hàm ROW trong Excel. Vì vậy, đối với ô F4, SMALL ({array}, 1) trả về phần tử đầu tiên (nhỏ nhất) của mảng, tức là 1. Đối với ô F5, nó trả về phần tử nhỏ thứ 2 của mảng, là 3, bật.
Phần 4 .INDEX ($ C $ 2: $ C $ 16, NHỎ (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 ô được chỉ định trong mảng C2: C16. Đối với ô F4, INDEX ($ C $ 2: $ C $ 16,1) trả về “Táo”; Đối với ô F5, INDEX ($ C $ 2: $ C $ 16,3) trả về “Kẹo”, v.v.
Phần 5 .IFERROR ()
Cuối cùng, hãy lồng công thức vào hàm IF trong excel vì bạn có thể không muốn thấy thông báo N / A trong bảng tính của mình 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ố các lần xuất hiện trùng lặp của giá trị tra cứu.
TẠI VÌ. Cách sử dụng kết hợp hàm Vlookup với Left. hàm số
Hàm Vlookup trong Excel: Là hàm tra cứu và trả 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ị được sử dụng để tra cứu
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ị.
Range_lookup: Phạm vi tìm kiếm TRUE tương đương với 1 (tra cứu tương đối), FALSE tương đương với 0 (tra cứu tuyệt đối).
Lưu ý: Đối với các giá trị lookup_value, hãy nhấn F4 3 lần và đối với các giá trị table_array, nhấn F4 một lần.
Ý NGHĨA CỦA VIỆC SỬ DỤNG F4
– F4 (1 lần): cho giá trị tuyệt đối. Tuyệt đối có nghĩa là cột cố định và dòng cố định ⇒ $ cột $ dòng
Ví dụ: $ A $ 8 hãy sửa cột A và sửa dòng 8
– F4 (2 lần): cho giá trị hàng tương đối và tuyệt đối của cột – Được hiểu là hàng cố định, không phải cột cố định cột $ row
Ví dụ: A $ 8 cố định hàng 8 sau đó không cố định cột A
– F4 (3 lần): cho giá trị tuyệt đối của hàng và cột tương đối – Được hiểu là cột cố định, không phải hàng cố định ⇒ cột $ hàng
CHỨC NĂNG TRÁI: Một hàm dùng để cắt 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 Left. hàm số
TRONG ĐÓ:
Văn bản: chuỗi ký tự cần cắt
N: số ký tự bạn muốn cắt
Bước 1: Điền vào ô C6 công thức: = LEFT (B6,2). Ý nghĩa của công thức trên là cắt 2 ký tự trong ô B6
Bước 2: Sau đó nhấn Enter. Kết quả đầu ra sẽ là 2 ký tự CN đã được cắt khỏi chuỗi ký tự IT1.
Bước 3: Bạn kéo từ ô C6 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 4: Bạn điền vào ô D6 hàm vlookup công thức: = 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 là phạm vi tìm kiếm tuyệt đối.
Bước 5: Sau đó nhấn Enter. Kết quả hiện ra là Tên ngành tương ứng với Mã ngành. Bạn lại kéo xuống dưới để các ô bên dưới tự động điền công thức và đưa ra kết quả tương ứng
Bước 6: Bạn điền vào ô E6 công thức hàm vlookup: = VLOOKUP (LEFT (B6,2), $ G $ 6: $ H $ 10,2,0). Hãy thay đổi các giá trị về cách sử dụng hàm Vlookup để tìm kiếm từ $ C6 sang LEFT (B6,2).
Bước 7: Sau đó nhấn nút Enter và thực hiện tương tự như bước 5.
VII. Tổng kết
Trên đây là hướng dẫn cách sử dụng hàm Vlookup và những thông tin hữu ích liên quan đến hàm Vlookup trong Excel. Hy vọng sau bài viết này các bạn có thể dễ dàng sử dụng các chức năng để thuận tiện hơn trong công việc văn phòng của mình. Chúc may mắn
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.
Comments