--- Bài mới hơn ---
Thao Tác Với Excel File Bằng Apache Poi
Đọc Ghi Excel File Sử Dụng Apache Poi
Java: Cách Tạo Và Chèn Dữ Liệu Vào File Excel
Làm Cách Nào Để Chèn Hoặc Nhúng Thư Outlook Vào Excel?
Cách Xem Nhanh Tệp Tin Đính Kèm Trong Outlook
Hàm VLOOKUP với một số ví dụ cơ bản và nâng cao trong Excel
Trong Excel, hàm VLOOKUP là một hàm mạnh mẽ đối với hầu hết người dùng Excel, được sử dụng để tìm kiếm giá trị ở ngoài cùng bên trái của phạm vi dữ liệu và trả về giá trị khớp trong cùng một hàng từ cột bạn đã chỉ định như ảnh chụp màn hình bên dưới . Hướng dẫn này nói về cách sử dụng hàm VLOOKUP với một số ví dụ cơ bản và nâng cao trong Excel.
Giới thiệu hàm VLOOKUP – Cú pháp và Đối số
Cú pháp của hàm VLOOKUP: =VLOOKUP (lookup_value, table_array, col_index_num, Sheet1′!$A$2:$B$6,2,0)
2. Sau đó, kéo và sao chép công thức này vào các ô khác mà bạn cần, xem ảnh chụp màn hình:
Ghi chú:
- 1. Trong công thức trên:
B2 đại diện cho giá trị tra cứu;
[Danh sách sản phẩm.xlsx] Trang tính 1 là tên của sổ làm việc và trang tính mà bạn muốn tra cứu dữ liệu, (Tham chiếu đến sổ làm việc được đặt trong dấu ngoặc vuông và toàn bộ sổ làm việc + trang tính được đặt trong dấu nháy đơn);
A2: B6 là phạm vi dữ liệu trong trang tính của sổ làm việc khác mà chúng tôi đang tìm kiếm dữ liệu;
con số 2 là số cột chứa dữ liệu phù hợp mà bạn muốn trả về.
- 2. Nếu sổ làm việc tra cứu bị đóng, đường dẫn tệp đầy đủ cho sổ làm việc tra cứu sẽ được hiển thị trong công thức như ảnh chụp màn hình sau:
9. Vlookup và trả về văn bản trống hoặc văn bản cụ thể thay vì giá trị lỗi 0 hoặc # N / A
Thông thường, khi bạn áp dụng hàm vlookup để trả về giá trị tương ứng, nếu ô phù hợp của bạn trống, nó sẽ trả về 0 và nếu không tìm thấy giá trị phù hợp của bạn, bạn sẽ gặp lỗi giá trị # N / A như ảnh chụp màn hình bên dưới. Thay vì hiển thị giá trị 0 hoặc # N / A với ô trống hoặc giá trị khác mà bạn thích, điều này Vlookup để trả lại giá trị trống hoặc giá trị cụ thể thay vì 0 hoặc không có hướng dẫn có thể giúp bạn từng bước một.
Ví dụ về VLOOKUP nâng cao
1. Tra cứu hai chiều với chức năng Vlookup (Vlookup trong hàng và cột)
Đôi khi, bạn có thể cần thực hiện tra cứu 2 chiều, nghĩa là Vlookup cả hàng và cột cùng một lúc. Giả sử, nếu bạn có phạm vi dữ liệu sau và bây giờ, bạn có thể cần nhận giá trị cho một sản phẩm cụ thể trong một quý cụ thể. Phần này sẽ giới thiệu một số công thức để xử lý công việc này trong Excel.
Trong Excel, bạn có thể sử dụng kết hợp các hàm Vlookup và MATCH để tra cứu hai chiều, vui lòng áp dụng công thức sau vào một ô trống, rồi nhấn Đi vào phím để nhận kết quả.
=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)
Lưu ý: Trong công thức trên:
-
H1: giá trị tra cứu trong cột mà bạn muốn lấy giá trị tương ứng dựa vào đó;
-
A2: E6: phạm vi dữ liệu bao gồm tiêu đề hàng;
-
H2: giá trị tra cứu trong hàng mà bạn muốn lấy giá trị tương ứng dựa trên;
-
A1: E1: các ô của tiêu đề cột.
=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))
Lưu ý: Trong công thức trên:
-
B2: E6: phạm vi dữ liệu để trả về mục phù hợp từ;
-
H1: giá trị tra cứu trong cột mà bạn muốn lấy giá trị tương ứng dựa vào đó;
-
A2: A6: tiêu đề hàng chứa sản phẩm bạn muốn tìm.
-
H2: giá trị tra cứu trong hàng mà bạn muốn lấy giá trị tương ứng dựa trên;
-
B1: E1: tiêu đề cột chứa phần tư bạn muốn tìm.
2. Giá trị khớp Vlookup dựa trên hai tiêu chí trở lên
Bạn có thể dễ dàng tra cứu giá trị phù hợp dựa trên một tiêu chí, nhưng nếu bạn có hai tiêu chí trở lên, bạn có thể làm gì? Các hàm LOOKUP hoặc MATCH và INDEX trong Excel có thể giúp bạn giải quyết công việc này một cách nhanh chóng và dễ dàng.
Ví dụ, tôi có bảng dữ liệu bên dưới, để trả lại giá phù hợp dựa trên sản phẩm và kích thước cụ thể, các công thức sau có thể giúp bạn.
=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12)) Ghi chú:
- 1. Trong công thức trên:
A2: A12 = G1: nghĩa là tìm kiếm các tiêu chí của G1 trong phạm vi A2: A12;
B2: B12 = G2: nghĩa là tìm kiếm các tiêu chí của G2 trong phạm vi B2: B12;
D2: D12: phạm vi mà bạn muốn trả về giá trị tương ứng.
- 2. Nếu bạn có nhiều hơn hai tiêu chí, bạn chỉ cần nối các tiêu chí khác vào công thức, chẳng hạn như: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
Sự kết hợp của chỉ mục và chức năng đối sánh cũng có thể được sử dụng để trả về giá trị phù hợp dựa trên nhiều tiêu chí. Vui lòng sao chép hoặc nhập công thức sau:
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))
Sau đó, nhấn tổ hợp phím Ctrl + Shift + Enter để nhận giá trị tương đối khi bạn cần. Xem ảnh chụp màn hình:
Ghi chú:
- 1. Trong công thức trên:
A2: A12 = G1: nghĩa là tìm kiếm các tiêu chí của G1 trong phạm vi A2: A12;
B2: B12 = G2: nghĩa là tìm kiếm các tiêu chí của G2 trong phạm vi B2: B12;
D2: D12: phạm vi mà bạn muốn trả về giá trị tương ứng.
- 2. Nếu bạn có nhiều hơn hai tiêu chí, bạn chỉ cần nối các tiêu chí mới vào công thức, chẳng hạn như: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
3. Vlookup để trả về nhiều giá trị phù hợp với một hoặc nhiều điều kiện
Trong Excel, hàm Vlookup tìm kiếm một giá trị và chỉ trả về giá trị khớp đầu tiên nếu có nhiều giá trị tương ứng được tìm thấy. Đôi khi, bạn có thể muốn trả về tất cả các giá trị tương ứng trong một hàng, trong một cột hoặc trong một ô. Phần này sẽ nói về cách trả về nhiều giá trị phù hợp với một hoặc nhiều điều kiện trong sổ làm việc.
Vlookup tất cả các giá trị phù hợp dựa trên một điều kiện theo chiều ngang:
Để Vlookup và trả về tất cả các giá trị phù hợp dựa trên một giá trị cụ thể theo chiều ngang, công thức chung là:
=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) – m, “”), COLUMN() – n)), “”)
Chú thích: m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1.
n là số cột của ô công thức đầu tiên trừ đi 1.
=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,””), COLUMN()-5)),””)
2. Và sau đó, chọn ô công thức đầu tiên và kéo chốt điền sang các ô bên phải cho đến khi ô trống được hiển thị và tất cả các mục tương ứng đã được trích xuất, xem ảnh chụp màn hình:
Lời khuyên:
Nếu có các giá trị phù hợp trùng lặp trong danh sách trả về, để bỏ qua các giá trị trùng lặp, vui lòng sử dụng công thức này, sau đó nhấn Đi vào để nhận được kết quả đầu tiên: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),””)
Tiếp tục nhập công thức này: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),””) vào một ô bên cạnh kết quả đầu tiên, rồi nhấn Ctrl + Shift + Enter các phím với nhau để nhận kết quả thứ hai, sau đó kéo công thức này sang các ô bên phải để nhận tất cả các giá trị phù hợp khác cho đến khi ô trống hiển thị, xem ảnh chụp màn hình:
Vlookup tất cả các giá trị phù hợp dựa trên hai hoặc nhiều điều kiện theo chiều ngang:
Để Vlookup và trả về tất cả các giá trị phù hợp dựa trên các giá trị cụ thể hơn theo chiều ngang, công thức chung là:
=IFERROR(INDEX(return_range, SMALL(IF(1 = ((–(lookup_value1=lookup_range1)) * (–(lookup_value2 = lookup_range2))), ROW(return_range) – m, “”), COLUMN() – n)),””)
Chú thích: m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1.
n là số cột của ô công thức đầu tiên trừ đi 1.
1. Áp dụng công thức sau vào một ô trống mà bạn muốn xuất kết quả:
=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((–($F1=$A$2:$A$20)) * (–($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,””), COLUMN()-5)),””)
2. Sau đó, chọn ô công thức và kéo chốt điền sang các ô bên phải cho đến khi ô trống hiển thị và tất cả các giá trị phù hợp dựa trên tiêu chí cụ thể đã được trả lại, xem ảnh chụp màn hình:
Chú thích: Để có thêm tiêu chí, bạn chỉ cần kết hợp lookup_value và lookup_range vào công thức, chẳng hạn như: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((–(lookup_value1=lookup_range1)) * (–(lookup_value2 = lookup_range2) * (–(lookup_value3 =lookup_range3)))), ROW(return_range) – m, “”), COLUMN() – n)),””) .
Vlookup tất cả các giá trị phù hợp dựa trên một điều kiện theo chiều dọc:
Để Vlookup và trả về tất cả các giá trị phù hợp dựa trên một giá trị cụ thể theo chiều dọc, công thức chung là:
=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,””), ROW() – n )),””)
Chú thích: m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1.
n là số hàng của ô công thức đầu tiên trừ đi 1.
=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,””), ROW()-1)),””)
2. Sau đó, chọn ô công thức đầu tiên và kéo chốt điền xuống các ô khác cho đến khi ô trống được hiển thị và tất cả các mục tương ứng đã được liệt kê trong một cột, xem ảnh chụp màn hình:
Sau đó nhấn Ctrl + Shift + Enter các phím với nhau để nhận giá trị phù hợp đầu tiên, sau đó kéo ô công thức này xuống các ô khác cho đến khi ô trống hiển thị và bạn sẽ nhận được kết quả như mong muốn:
Vlookup tất cả các giá trị phù hợp dựa trên hai hoặc nhiều điều kiện theo chiều dọc:
Để Vlookup và trả về tất cả các giá trị phù hợp dựa trên các giá trị cụ thể hơn theo chiều dọc, công thức chung là:
=IFERROR(INDEX(return_range, SMALL(IF(1=((–(lookup_value1=lookup_range1)) * ( –(lookup_value2=lookup_range2))), ROW(return_range)-m,””), ROW()-n)),””)
Chú thích: m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1.
n là số hàng của ô công thức đầu tiên trừ đi 1.
=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((–($E$2=$A$2:$A$20)) * (–($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,””), ROW()-1)),””)
2. Sau đó kéo ô công thức xuống các ô khác cho đến khi ô trống được hiển thị, xem ảnh chụp màn hình:
Chú thích: Để có thêm tiêu chí, bạn chỉ cần kết hợp lookup_value và lookup_range vào công thức, chẳng hạn như: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((–(lookup_value1=lookup_range1)) * (–(lookup_value2 = lookup_range2) * (–(lookup_value3 =lookup_range3)))), ROW(return_range) – m, “”), ROW() – n)),””) .
Nếu bạn muốn Vlookup và trả về nhiều giá trị đã so khớp vào một ô duy nhất với dấu phân cách được chỉ định, chức năng mới của TEXTJOIN có thể giúp bạn giải quyết công việc này một cách nhanh chóng và dễ dàng.
Vlookup tất cả các giá trị phù hợp dựa trên một điều kiện vào một ô duy nhất:
=TEXTJOIN(“,”,TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,””)) Lời khuyên:
Để bỏ qua các bản sao trong các giá trị phù hợp được trả về, vui lòng sử dụng công thức sau: =TEXTJOIN(“,”, TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, “”), 0),””)=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, “”))
Vlookup tất cả các giá trị phù hợp dựa trên hai hoặc nhiều điều kiện vào một ô duy nhất:
=TEXTJOIN(“,”,TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,””)) Ghi chú:
1. Hàm TEXTJOIN chỉ khả dụng trong Excel 2022 và Office 365.
4. Vlookup để trả về toàn bộ hoặc toàn bộ hàng của một ô phù hợp
Trong phần này, tôi sẽ nói về cách lấy toàn bộ hàng của một giá trị phù hợp bằng cách sử dụng hàm Vlookup.
=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)
2. Sau đó, kéo ô công thức sang bên phải cho đến khi dữ liệu của toàn bộ hàng được hiển thị, xem ảnh chụp màn hình:
Lời khuyên:
Nếu tìm thấy nhiều hàng dựa trên giá trị phù hợp, để trả về tất cả các hàng tương ứng, vui lòng áp dụng công thức này: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),””),ROW()-1)),””) , và sau đó nhấn Ctrl + Shift + Enter các phím với nhau để nhận được kết quả đầu tiên, sau đó kéo chốt điền sang phải các ô, xem ảnh chụp màn hình:
5. Thực hiện nhiều hàm Vlookup (Vlookup lồng nhau) trong Excel
Công thức chung cho hàm Vlookup lồng nhau là:
=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0))) Lưu ý:
-
lookup_value: giá trị bạn đang tìm kiếm;
-
Bảng1, Bảng2, Bảng3, …: bảng chứa giá trị tra cứu và giá trị trả về;
-
col: số cột trong bảng mà bạn muốn trả về giá trị phù hợp.
-
0: Điều này được sử dụng cho một kết hợp chính xác.
1. Vui lòng áp dụng công thức sau vào một ô trống mà bạn muốn đặt kết quả:
Ghi chú:
- 1. Trong công thức trên, J3 là giá trị bạn đang tìm kiếm; A3: B7, D3: E7, G3: H7 là các phạm vi bảng trong đó giá trị tra cứu và giá trị trả về tồn tại; Con số 2 là số cột trong phạm vi để trả về giá trị phù hợp.
- 2. Nếu không tìm thấy giá trị tra cứu, giá trị lỗi sẽ được hiển thị, để thay thế lỗi bằng một văn bản có thể đọc được, vui lòng sử dụng công thức sau: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),”can’t find”)))
6. Vlookup để kiểm tra xem giá trị có tồn tại hay không dựa trên dữ liệu danh sách trong một cột khác
Chức năng Vlookup cũng có thể giúp bạn kiểm tra xem các giá trị có tồn tại hay không dựa trên một danh sách khác, ví dụ: nếu bạn muốn tìm tên trong cột C và chỉ cần trả về Có hoặc Không nếu tên được tìm thấy hay không trong cột A như ảnh chụp màn hình bên dưới cho xem.
1. Vui lòng áp dụng công thức sau vào một ô trống:
=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), “No”, “Yes”)
2. Sau đó, kéo chốt điền xuống các ô mà bạn muốn điền vào công thức này và bạn sẽ nhận được kết quả như mong muốn, xem ảnh chụp màn hình:
7. Vlookup và tổng hợp tất cả các giá trị phù hợp trong các hàng hoặc cột
Nếu bạn làm việc với dữ liệu số, đôi khi, khi trích xuất các giá trị phù hợp từ bảng, bạn cũng có thể cần tính tổng các số trong một số cột hoặc hàng. Phần này sẽ giới thiệu một số công thức để hoàn thành công việc này trong Excel.
Giả sử, bạn có một danh sách sản phẩm với doanh số bán hàng trong vài tháng như ảnh chụp màn hình bên dưới, bây giờ, bạn cần tổng hợp tất cả các đơn đặt hàng trong tất cả các tháng dựa trên các sản phẩm đã cho.
Vlookup và tính tổng các giá trị phù hợp đầu tiên trong một hàng:
=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))
2. Sau đó, kéo chốt điền xuống để sao chép công thức này sang các ô khác mà bạn cần và tất cả các giá trị trong một hàng của giá trị khớp đầu tiên đã được cộng lại với nhau, xem ảnh chụp màn hình:
Vlookup và tính tổng tất cả các giá trị phù hợp trong nhiều hàng:
Công thức trên chỉ có thể tính tổng các giá trị trong một hàng cho giá trị phù hợp đầu tiên. Nếu bạn muốn tính tổng tất cả các kết quả phù hợp trong nhiều hàng, vui lòng sử dụng công thức sau, sau đó kéo chốt điền xuống các ô bạn muốn áp dụng công thức này và bạn sẽ nhận được kết quả mong muốn, xem ảnh chụp màn hình:
=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
Vlookup và tính tổng các giá trị phù hợp đầu tiên trong một cột:
Nếu bạn muốn tính tổng giá trị cho các tháng cụ thể như trong ảnh chụp màn hình bên dưới.
=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))
Vlookup và tính tổng tất cả các giá trị phù hợp trong nhiều cột:
Để Vlookup và tính tổng tất cả các giá trị phù hợp trong nhiều cột, bạn nên sử dụng công thức sau:
=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
Có thể các công thức trên khó nhớ đối với bạn, trong trường hợp này, tôi sẽ giới thiệu một tính năng tiện dụng – Tra cứu và tính tổng of Kutools cho Excel, với tính năng này, bạn có thể nhận được kết quả dễ dàng nhất có thể. Nhấp để tải xuống Kutools cho Excel ngay bây giờ!
Ví dụ, nếu bạn muốn tính tổng các giá trị khi bạn cần khớp cả cột và hàng để có được tổng giá trị của sản phẩm Áo len trong tháng XNUMX như ảnh chụp màn hình bên dưới.
Vui lòng áp dụng công thức sau vào một ô, sau đó nhấn phím Enter để nhận kết quả, xem ảnh chụp màn hình:
=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))
Chú thích: Trong công thức trên: B2: F9 là phạm vi dữ liệu của các giá trị số mà bạn muốn tính tổng; B1: F1 istiêu đề cột chứa giá trị tra cứu mà bạn muốn tính tổng dựa trên; I2 là giá trị tra cứu trong tiêu đề cột bạn đang tìm kiếm; A2: A9 là tiêu đề hàng chứa giá trị tra cứu mà bạn muốn tính tổng dựa trên; H2 là giá trị tra cứu trong tiêu đề hàng bạn đang tìm kiếm.
8. Vlookup để hợp nhất hai bảng dựa trên một hoặc nhiều cột chính
Trong công việc hàng ngày, khi phân tích dữ liệu, bạn có thể cần tập hợp tất cả thông tin cần thiết vào một bảng duy nhất dựa trên một hoặc nhiều cột chính. Để giải quyết công việc này, chức năng Vlookup cũng có thể giúp bạn.
Ví dụ: bạn có hai bảng, bảng đầu tiên chứa dữ liệu về sản phẩm và tên, và bảng thứ hai chứa sản phẩm và đơn đặt hàng, bây giờ, bạn muốn kết hợp hai bảng này bằng cách khớp cột sản phẩm chung thành một bảng.
Để hợp nhất hai bảng thành một dựa trên một cột chính, vui lòng áp dụng công thức sau vào một ô trống mà bạn muốn lấy kết quả, sau đó kéo chốt điền xuống các ô mà bạn muốn áp dụng công thức này, bạn sẽ lấy bảng đã hợp nhất với cột thứ tự nối với dữ liệu bảng đầu tiên dựa trên dữ liệu cột chính.
=VLOOKUP($A2,$E$2:$F$8,2,FALSE)
Nếu dữ liệu chung của bạn ở phía bên phải và dữ liệu trả về ở cột bên trái trong bảng thứ hai, để hợp nhất cột thứ tự, thì hàm Vlookup không thể thực hiện công việc. Để tra cứu từ phải sang trái, bạn có thể sử dụng hàm INDEX và MATCH để thay thế cho hàm Vlookup.
=INDEX($E$2:$E$8, MATCH($A2, $F$2:$F$8, 0))
Nếu hai bảng bạn muốn nối có nhiều cột chính, để hợp nhất các bảng dựa trên các cột chung này, hàm INDEX và MATCH có thể giúp bạn.
Công thức chung để hợp nhất hai bảng dựa trên nhiều cột chính là:
2 Sau đó, chọn ô công thức đầu tiên và kéo chốt điền để sao chép công thức này sang các ô khác khi bạn cần:
Lời khuyên: Trong Excel 2022 và các phiên bản mới hơn, bạn cũng có thể sử dụng Truy vấn nguồn tính năng hợp nhất hai hoặc nhiều bảng thành một bảng dựa trên các cột chính. Vui lòng nhấp để biết chi tiết từng bước.
9. Vlookup so khớp các giá trị trên nhiều trang tính
Các giá trị phù hợp với hàm VLOOKUP giữ nguyên định dạng ô
1. Vlookup để nhận định dạng ô (màu ô, màu phông chữ) cùng với giá trị tra cứu
Vui lòng thực hiện theo các bước sau để tra cứu và trả về giá trị tương ứng của nó cùng với định dạng ô:
1. Trong trang tính có chứa dữ liệu bạn muốn Vlookup, nhấp chuột phải vào tab trang tính và chọn Mã Chế độ xem từ menu ngữ cảnh. Xem ảnh chụp màn hình:
2. Trong mở Microsoft Visual Basic cho các ứng dụng , vui lòng sao chép mã VBA bên dưới vào cửa sổ Mã.
Mã VBA 1: Vlookup để nhận định dạng ô cùng với giá trị tra cứu
Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
Dim I As Long
Dim xKeys As Long
Dim xDicStr As String
On Error Resume Next
Application.ScreenUpdating = False
xKeys = UBound(xDic.Keys)
For I = 0 To UBound(xDic.Keys)
xDicStr = xDic.Items(I)
Range(xDic.Keys(I)).Interior.Color = _
Range(xDic.Items(I)).Interior.Color
Range(xDic.Keys(I)).Font.FontStyle = _
Range(xDic.Items(I)).Font.FontStyle
Range(xDic.Keys(I)).Font.Size = _
Range(xDic.Items(I)).Font.Size
Range(xDic.Keys(I)).Font.Color = _
Range(xDic.Items(I)).Font.Color
Range(xDic.Keys(I)).Font.Name = _
Range(xDic.Items(I)).Font.Name
Range(xDic.Keys(I)).Font.Underline = _
Range(xDic.Items(I)).Font.Underline
Else
Range(xDic.Keys(I)).Interior.Color = xlNone
End If
Next
Set xDic = Nothing
End If
Application.ScreenUpdating = True
End Sub
Mã VBA 2: Vlookup để nhận định dạng ô cùng với giá trị tra cứu
Public xDic As New Dictionary
Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As Range
On Error Resume Next
Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
If xFindCell Is Nothing Then
LookupKeepFormat = ""
chúng tôi Application.Caller.Address, ""
Else
LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
chúng tôi Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
End If
End Function
5. Sau đó nhấn vào OK để đóng hộp thoại, sau đó lưu và đóng cửa sổ mã, bây giờ, hãy quay lại trang tính, rồi áp dụng công thức này: =LookupKeepFormat(E2,$A$1:$C$10,3) vào một ô trống mà bạn muốn xuất kết quả, rồi nhấn phím Enter. Xem ảnh chụp màn hình:
6. Sau đó, chọn ô kết quả đầu tiên và kéo chốt điền xuống để nhận tất cả kết quả cùng với định dạng của chúng. Xem ảnh chụp màn hình.
2. Giữ định dạng ngày từ giá trị trả về Vlookup
=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),”mm/dd/yyyy”)
Chú thích: Trong công thức trên, E2 là giá trị giao diện, A2: C9 là phạm vi tra cứu, số 3 là số cột bạn muốn giá trị được trả về, mm/dd/yyy là định dạng ngày bạn muốn giữ lại.
3. Vlookup và trả về giá trị khớp với nhận xét ô
Bạn đã bao giờ thử dùng Vlookup để trả về không chỉ dữ liệu ô phù hợp mà còn cả nhận xét ô cũng như trong Excel như ảnh chụp màn hình sau đây không? Để giải quyết nhiệm vụ này, Hàm do Người dùng Xác định bên dưới có thể giúp bạn.
1. Giữ ALT + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
Mã VBA: Vlookup và trả về giá trị khớp với nhận xét ô:
Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
Application.Volatile
Dim xRet As Variant 'could be an error
Dim xCell As Range
xRet = Application.Match(LookVal, FTable.Columns(1), FType)
If IsError(xRet) Then
VlookupComment = "Not Found"
Else
Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
VlookupComment = xCell.Value
With Application.Caller
If Not .Comment Is Nothing Then
.Comment.Delete
End If
If Not xCell.Comment Is Nothing Then
.AddComment xCell.Comment.Text
End If
End With
End If
End Function
4. Xử lý văn bản và số thực trong Vlookup
Ví dụ: tôi có một dải dữ liệu, số ID trong bảng gốc là định dạng số, trong ô tra cứu được lưu trữ dưới dạng văn bản, khi áp dụng chức năng Vlookup thông thường, kết quả lỗi # N / A được hiển thị như ảnh chụp màn hình bên dưới cho xem. Trong trường hợp này, làm thế nào bạn có thể nhận được thông tin chính xác nếu số tra cứu và số gốc trong bảng có định dạng dữ liệu khác nhau?
Để xử lý văn bản và số thực trong hàm Vlookup, vui lòng áp dụng công thức sau vào một ô trống, sau đó kéo chốt điền xuống để sao chép công thức này và bạn sẽ nhận được kết quả chính xác như ảnh chụp màn hình bên dưới:
=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0)) Ghi chú:
- 1. Trong công thức trên, D2 là giá trị tra cứu bạn muốn trả về giá trị tương ứng của nó, A2: B8 là bảng dữ liệu bạn muốn sử dụng, số 2 là số cột chứa giá trị phù hợp mà bạn muốn trả về.
- 2. Công thức này cũng hoạt động tốt nếu bạn không chắc chắn nơi bạn có số và nơi bạn có văn bản.
Tải xuống các tệp mẫu VLOOKUP
- Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
- Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
- Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
--- Bài cũ hơn ---
Làm Cách Nào Để Đếm / Chỉ Tính Tổng Các Số Dương Hoặc Số Âm Trong Excel?
Hướng Dẫn Cách Dùng Hàm Nối Ký Tự Trong Excel
Hướng Dẫn Nối Dữ Liệu Nhiều Ô Trong Excel
Hướng Dẫn Cách Gộp Nhiều Sheet Vào Một Sheet Tổng Hợp Bằng Vba Có Code Đính Kèm
Tìm Giá Trị Lớn Nhất