Hàm VLOOKUP với một số ví dụ cơ bản và nâng cao trong Excel

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

, [range_lookup])

đối số:

Lookup_value: Giá trị mà bạn muốn tìm kiếm. Nó phải nằm trong cột đầu tiên của phạm vi table_array.

Table_array: Phạm vi dữ liệu hoặc bảng nơi định vị cột giá trị tra cứu và cột giá trị kết quả.

Col_index_num: Số cột mà từ đó giá trị phù hợp sẽ được trả về. Nó bắt đầu bằng 1 từ cột ngoài cùng bên trái trong mảng bảng.

Range_lookup: Giá trị logic xác định xem hàm VLOOKUP này sẽ trả về kết quả khớp chính xác hay kết quả gần đúng.

• Đối sánh gần đúng – XNUMX – 1 / ĐÚNG: Nếu không tìm thấy kết hợp chính xác, công thức sẽ tìm kiếm kết hợp gần nhất – giá trị lớn nhất nhỏ hơn giá trị tra cứu. Trong trường hợp này, bạn nên sắp xếp cột tra cứu theo thứ tự tăng dần.
  = VLOOKUP (lookup_value, table_array, col_index, TRUE)
  = VLOOKUP (lookup_value, table_array, col_index, 1)
• Kết hợp chuẩn xác – XNUMX – 0 / SAI: Điều này được sử dụng để tìm kiếm một giá trị chính xác bằng giá trị tra cứu. Nếu không tìm thấy kết quả khớp chính xác, giá trị lỗi # N / A sẽ được trả về.
  = VLOOKUP (lookup_value, table_array, col_index, FALSE)
  = VLOOKUP (lookup_value, table_array, col_index, 0)

Ghi chú:

• 1. Hàm Vlookup chỉ tìm kiếm giá trị từ trái sang phải.
• 2. Nếu có nhiều giá trị khớp dựa trên giá trị tra cứu, thì chỉ giá trị khớp đầu tiên sẽ được trả về bằng cách sử dụng hàm Vlookup.
• 3. Nó sẽ trả về giá trị lỗi # N / A nếu không tìm thấy giá trị tra cứu.

Các ví dụ cơ bản về VLOOKUP

1. So khớp chính xác Vlookup và so khớp gần đúng Vlookup

Thực hiện đối sánh chính xác Vlookup trong Excel

Thông thường, nếu bạn đang tìm kiếm kết hợp chính xác với hàm Vlookup, bạn chỉ cần sử dụng FALSE trong đối số cuối cùng.

Ví dụ, để nhận được điểm Toán tương ứng dựa trên số ID cụ thể, vui lòng thực hiện như sau:

1. Áp dụng công thức dưới đây vào một ô trống mà bạn muốn nhận kết quả:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

2. Và sau đó, kéo chốt điền xuống các ô 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:

Ghi chú:

• 1. Trong công thức trên, F2 là giá trị mà bạn muốn trả về giá trị phù hợp của nó, A2: D7 là mảng bảng, số 3 là số cột mà giá trị phù hợp của bạn được trả về và KHÔNG ĐÚNG đề cập đến sự phù hợp chính xác.
• 2. Nếu giá trị tiêu chí của bạn không được tìm thấy trong phạm vi dữ liệu, giá trị lỗi # N / A sẽ được hiển thị.

Thực hiện đối sánh gần đúng Vlookup trong Excel

Đối sánh gần đúng hữu ích cho việc tìm kiếm giá trị giữa các phạm vi dữ liệu. Nếu không tìm thấy kết quả khớp chính xác, thì Vlookup gần đúng sẽ trả về giá trị lớn nhất nhỏ hơn giá trị tra cứu.

Ví dụ: nếu bạn có dữ liệu phạm vi sau đây, các đơn hàng được chỉ định không có trong cột Đơn hàng, làm thế nào để nhận được Giảm giá gần nhất của nó trong cột B?

1. Nhập công thức sau vào một ô mà bạn muốn đặt kết quả:

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

2. Sau đó, kéo chốt điền xuống các ô để áp dụng công thức này và bạn sẽ nhận được các kết quả phù hợp gần đúng dựa trên các giá trị đã cho, xem ảnh chụp màn hình:

Ghi chú:

• 1. Trong công thức trên, D2 là giá trị mà bạn muốn trả về thông tin tương đối của nó, A2: B9 là phạm vi dữ liệu, số 2 cho biết số cột mà giá trị phù hợp của bạn được trả lại và TRUE đề cập đến kết quả phù hợp gần đúng.
• 2. Đối sánh gần đúng sẽ trả về giá trị lớn nhất nhỏ hơn giá trị tra cứu cụ thể của bạn.
• 3. Để sử dụng hàm Vlookup để nhận giá trị khớp gần đúng, bạn phải sắp xếp cột ngoài cùng bên trái của dải dữ liệu theo thứ tự tăng dần, nếu không sẽ trả về kết quả sai.

2. Làm một Vlookup phân biệt chữ hoa chữ thường trong Excel

Theo mặc định, hàm Vlookup thực hiện tra cứu không phân biệt chữ hoa chữ thường có nghĩa là nó xử lý các ký tự viết thường và viết hoa là giống hệt nhau. Đôi khi, bạn có thể cần phải thực hiện tra cứu phân biệt chữ hoa chữ thường trong Excel, các hàm Chỉ mục, Khớp và Chính xác hoặc các hàm Tra cứu và Chính xác có thể giúp bạn.

Ví dụ: tôi có dải dữ liệu sau mà cột ID chứa chuỗi văn bản với chữ hoa hoặc chữ thường, bây giờ, tôi muốn trả về điểm Toán tương ứng của số ID đã cho.

Công thức 1: Sử dụng các hàm EXACT, INDEX, MATCH

1. Vui lòng nhập hoặc sao chép công thức mảng dưới đây vào ô trống mà bạn muốn nhận kết quả:

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

2. Sau đó nhấn Ctrl + Shift + Enter các phím đồng thời để lấy kết quả đầu tiên, sau đó chọn ô công thức, kéo chốt điền xuống các ô bạn muốn điền công thức này, sau đó bạn sẽ nhận được kết quả chính xác mà bạn cần. Xem ảnh chụp màn hình:

Ghi chú:

• 1. Trong công thức trên, A2: A10 là cột chứa các giá trị cụ thể mà bạn muốn tra cứu, F2 là giá trị tra cứu, C2: C10 là cột nơi kết quả sẽ được trả về.
• 2. Nếu tìm thấy nhiều kết quả phù hợp, công thức này sẽ luôn trả về kết quả phù hợp đầu tiên.

Công thức 2: Sử dụng các hàm Tra cứu và Chính xác

1. Vui lòng áp dụng công thức dưới đây vào ô trống mà bạn muốn nhận kết quả:

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

2. Sau đó, kéo chốt điền xuống các ô mà bạn muốn sao chép công thức này và bạn sẽ nhận được các giá trị phù hợp có phân biệt chữ hoa chữ thường như ảnh chụp màn hình bên dưới:

Ghi chú:

• 1. Trong công thức trên, A2: A10 là cột chứa các giá trị cụ thể mà bạn muốn tra cứu, F2 là giá trị tra cứu, C2: C10 là cột nơi kết quả sẽ được trả về.
• 2. Nếu tìm thấy nhiều kết quả phù hợp, công thức này sẽ luôn trả về kết quả phù hợp cuối cùng.

3. Giá trị Vlookup từ phải sang trái trong Excel

Hàm Vlookup luôn tìm kiếm một giá trị ở cột ngoài cùng bên trái của một dải dữ liệu và trả về giá trị tương ứng từ cột bên phải. Nếu bạn muốn thực hiện một Vlookup ngược có nghĩa là tra cứu một giá trị cụ thể ở bên phải và trả về giá trị tương ứng của nó ở cột bên trái như hình minh họa bên dưới:

Nhấp để biết chi tiết từng bước về nhiệm vụ này…

4. Vlookup giá trị khớp thứ hai, thứ n hoặc cuối cùng trong Excel

Thông thường, nếu có nhiều giá trị khớp được tìm thấy khi sử dụng hàm Vlookup, chỉ bản ghi khớp đầu tiên sẽ được trả về. Trong phần này, tôi sẽ nói về cách lấy giá trị khớp thứ hai, thứ n hoặc cuối cùng với hàm Vlookup.

Vlookup và trả về giá trị khớp thứ hai hoặc thứ n

Giả sử bạn có một danh sách tên trong cột A, khóa đào tạo mà họ đã mua trong cột B và bây giờ, bạn đang tìm kiếm khóa đào tạo thứ 2 hoặc thứ n mà khách hàng đã mua. Xem ảnh chụp màn hình:

1. Để nhận giá trị khớp thứ hai hoặc thứ n dựa trên tiêu chí đã cho, vui lòng áp dụng công thức mảng sau vào ô trống:

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

2. Sau đó nhấn Ctrl + Shift + Enter các phím với nhau để nhận kết quả đầu tiên, rồi chọn ô công thức, kéo chốt điền xuống các ô bạn muốn điền công thức này và tất cả các giá trị phù hợp thứ hai dựa trên các tên đã cho đã được hiển thị cùng một lúc, xem ảnh chụp màn hình:

Lưu ý:

• Trong công thức này, A2: A14 là phạm vi có tất cả các giá trị để tra cứu, B2: B14 là phạm vi của các giá trị phù hợp mà bạn muốn trả về, E2 là giá trị tra cứu và số cuối cùng 2 cho biết giá trị khớp thứ hai mà bạn muốn lấy, nếu bạn muốn muốn trả về giá trị khớp thứ ba, bạn chỉ cần thay đổi nó thành 3 khi bạn cần.

Vlookup và trả về giá trị khớp cuối cùng

Nếu bạn muốn vlookup và trả về giá trị khớp cuối cùng như hình ảnh chụp màn hình bên dưới, điều này Vlookup và trả lại giá trị phù hợp cuối cùng hướng dẫn có thể giúp bạn nhận được giá trị phù hợp cuối cùng trong chi tiết.

5. Vlookup so khớp các giá trị giữa hai giá trị hoặc ngày nhất định

Đôi khi, bạn có thể muốn tra cứu giá trị giữa hai giá trị hoặc ngày tháng và trả về kết quả tương ứng như hình minh họa bên dưới, trong trường hợp này, bạn có thể sử dụng hàm LOOKUP và bảng đã sắp xếp.

Vlookup so khớp các giá trị giữa hai giá trị hoặc ngày đã cho với công thức

1. Đầu tiên, bảng gốc của bạn phải là một dải dữ liệu được sắp xếp. Và sau đó, sao chép hoặc nhập công thức sau vào một ô trống:

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

2. Sau đó, kéo chốt điền để điền công thức này vào các ô khác mà bạn cần và bây giờ, bạn sẽ nhận được tất cả các bản ghi phù hợp dựa trên giá trị đã cho, xem ảnh chụp màn hình:

Ghi chú:

• 1. Trong công thức trên, A2: A6 là phạm vi các giá trị nhỏ hơn và B2: B6 là phạm vi các số lớn hơn trong phạm vi dữ liệu của bạn, E2 là giá trị đã cho mà bạn muốn nhận giá trị tương ứng của nó, C2: C6 là dữ liệu cột mà bạn muốn trích xuất.
• 2. Công thức này cũng có thể được sử dụng để trích xuất các giá trị phù hợp giữa hai ngày như ảnh chụp màn hình dưới đây:
  

Vlookup so khớp các giá trị giữa hai giá trị hoặc ngày nhất định với một tính năng hữu ích

Nếu bạn đang đau đầu với công thức trên, thì đây, tôi sẽ giới thiệu một công cụ dễ dàng – Kutools cho Excel, Với khả NHÌN giữa hai giá trị tính năng, bạn có thể trả lại mục tương ứng dựa trên giá trị hoặc ngày cụ thể giữa hai giá trị hoặc ngày tháng mà không cần nhớ bất kỳ công thức nào.   Nhấp để tải xuống Kutools cho Excel ngay bây giờ!

6. Sử dụng các ký tự đại diện cho các kết quả phù hợp từng phần trong hàm Vlookup

Trong Excel, các ký tự đại diện có thể được sử dụng trong hàm Vlookup, hàm này giúp thực hiện đối sánh một phần giá trị tra cứu. Ví dụ: bạn có thể sử dụng Vlookup để trả về giá trị phù hợp từ một bảng dựa trên một phần của giá trị tra cứu.

Giả sử, tôi có một loạt dữ liệu như ảnh chụp màn hình bên dưới, bây giờ, tôi muốn trích xuất điểm số dựa trên tên (không phải tên đầy đủ). Làm cách nào để giải quyết công việc này trong Excel?

1. Chức năng Vlookup bình thường không hoạt động chính xác, bạn cần kết hợp văn bản hoặc tham chiếu ô với ký tự đại diện, vui lòng sao chép hoặc nhập công thức sau vào ô trống:

=VLOOKUP(E2&”*”, $A$2:$C$11, 3, FALSE)

2. Sau đó, kéo chốt điền để điền công thức này vào các ô khác mà bạn cần và tất cả các điểm phù hợp đã được trả lại như hình minh họa bên dưới:

Ghi chú:

• 1. Trong công thức trên, E2 & ”*” là giá trị tra cứu, giá trị trong E2 và * ký tự đại diện (“*” cho biết bất kỳ một ký tự nào hoặc bất kỳ ký tự nào), A2: C11 là phạm vi tra cứu, số 3 cột chứa giá trị trả về.
• 2. Vlookup khi sử dụng ký tự đại diện, bạn phải đặt chế độ đối sánh chính xác với FALSE hoặc 0 cho đối số cuối cùng trong hàm Vlookup.

Lời khuyên:

1. Tìm và trả về các giá trị phù hợp kết thúc bằng một giá trị cụ thể, vui lòng áp dụng công thức này: =VLOOKUP(“*”&E2, $A$2:$C$11, 3, FALSE)

2. Để tra cứu và trả về giá trị đã so khớp dựa trên một phần của chuỗi văn bản, cho dù văn bản được chỉ định ở phía trước, phía sau hay ở giữa chuỗi văn bản, bạn chỉ cần nối hai ký tự * xung quanh tham chiếu ô hoặc văn bản. Vui lòng làm với công thức này: =VLOOKUP(“*”&D2&”*”, $A$2:$B$11, 2, FALSE)

7. Giá trị Vlookup từ một trang tính khác

Thông thường, bạn có thể phải làm việc với nhiều trang tính, hàm Vlookup có thể được sử dụng để tra cứu dữ liệu từ một trang tính khác giống như trên một trang tính.

Ví dụ: bạn có hai trang tính như hình minh họa bên dưới, để tra cứu và trả về dữ liệu tương ứng từ trang tính bạn đã chỉ định, vui lòng thực hiện theo các bước sau:

1. Vui lòng nhập hoặc sao chép công thức dưới đây vào ô trống nơi bạn muốn lấy các mục phù hợp:

=VLOOKUP(A2,’Data sheet’!$A$2:$C$15,3,0)

2. Sau đó, kéo chốt điều khiển điền xuống các ô mà bạn muốn áp dụng công thức này và bạn sẽ nhận được kết quả tương ứng như bạn cần, xem ảnh chụp màn hình:

Lưu ý: Trong công thức trên:

• A2 đại diện cho giá trị tra cứu;
• Bảng dữliệu là tên của trang tính mà bạn muốn tra cứu dữ liệu, (Nếu tên trang tính chứa các ký tự khoảng trắng hoặc dấu chấm câu, bạn nên đặt dấu ngoặc kép xung quanh tên trang tính, nếu không, bạn có thể sử dụng trực tiếp tên trang tính như = VLOOKUP (A2, Biểu dữ liệu! $ A $ 2: $ C $ 15,3,0));
• A2: C15 là phạm vi dữ liệu trong Bảng dữ liệu mà chúng tôi đang tìm kiếm dữ liệu;
• con số 3 là số cột chứa dữ liệu phù hợp mà bạn muốn trả về.

8. Giá trị Vlookup từ sổ làm việc khác

Phần này sẽ nói về việc tra cứu và trả về các giá trị phù hợp từ một sổ làm việc khác bằng cách sử dụng hàm Vlookup.

Ví dụ: sổ làm việc đầu tiên chứa danh sách sản phẩm và chi phí, bây giờ, bạn muốn trích xuất chi phí tương ứng trong sổ làm việc thứ hai dựa trên mục sản phẩm như hình minh họa bên dưới.

1. Để truy xuất chi phí tương đối từ một sổ làm việc khác, trước tiên, hãy mở cả hai sổ làm việc bạn muốn sử dụng, sau đó áp dụng công thức sau vào một ô mà bạn muốn đặt kết quả:

=VLOOKUP(B2,'[Product list.xlsx]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.

Công thức 1: Sử dụng hàm VLOOKUP và MATCH

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.

Công thức 2: Sử dụng hàm INDEX và MATCH

Đây là một công thức khác cũng có thể giúp bạn thực hiện tra cứu 2 chiều, vui lòng áp dụng công thức dưới đây, sau đó nhấn Đi vào chìa khóa để nhận được kết quả bạn cần.

=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.

Công thức 1: Sử dụng hàm LOOKUP

Vui lòng áp dụng công thức dưới đây vào một ô mà bạn muốn lấy kết quả, sau đó nhấn phím Enter, xem ảnh chụp màn hình:

=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))

  

Công thức 2: Sử dụng hàm INDEXT và MATCH

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 hoặc nhiều điều kiện theo chiều ngang

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.

1. Vui lòng áp dụng công thức dưới đây vào một ô trống, sau đó nhấn Ctrl + Shift + Enter các phím với nhau để nhận giá trị phù hợp đầu tiên, xem ảnh chụp màn hình:

=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 hoặc nhiều điều kiện theo chiều dọ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 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.

1. Sao chép hoặc nhập công thức sau vào một ô mà bạn muốn nhận kết quả, rồi nhấn Ctrl + Shift + Enter các phím với nhau để nhận giá trị phù hợp đầu tiên, xem ảnh chụp màn hình:

=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:

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: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),””)

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.

1. Sao chép công thức dưới đây vào một ô trống, sau đó nhấn Ctrl + Shift + Enter các phím với nhau để lấy vật phẩm phù hợp đầu tiên.

=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)),””).

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

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:

Vui lòng áp dụng công thức đơn giản dưới đây vào một ô trống, sau đó nhấn Ctrl + Shift + Enter các phím với nhau để nhận được kết quả:

=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:

Để xử lý nhiều điều kiện khi trả về tất cả các giá trị phù hợp vào một ô, vui lòng áp dụng công thức dưới đây, rồi nhấn Ctrl + Shift + Enter các phím với nhau để nhận được kết quả:

=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 2019 và Office 365.

2. Nếu bạn sử dụng Excel 2016 và các phiên bản cũ hơn, vui lòng sử dụng Hàm do người dùng xác định trong các bài viết dưới đây:

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.

1. Vui lòng sao chép hoặc nhập công thức dưới đây vào một ô trống mà bạn muốn xuất kết quả và nhấn Đi vào phím để nhận giá trị đầu tiên.

=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:

Chú thích: Trong công thức trên, F2 là giá trị tra cứu bạn muốn trả về toàn bộ hàng dựa trên, A1: D12 là phạm vi dữ liệu bạn muốn sử dụng, A1 cho biết số cột đầu tiên trong phạm vi dữ liệu của bạn.

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:

Và sau đó kéo chốt điền xuống trên các ô để nhận được tất cả các hàng phù hợp như hình minh họa bên dưới:

5. Thực hiện nhiều hàm Vlookup (Vlookup lồng nhau) trong Excel

Đôi khi, bạn có thể muốn tra cứu các giá trị trong nhiều bảng, nếu bất kỳ bảng nào chứa giá trị tra cứu đã cho như hình minh họa bên dưới, trong trường hợp này, bạn có thể kết hợp một hoặc nhiều hàm Vlookup cùng với hàm IFERROR để thực hiện nhiều tra cứu.

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ả:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Sau đó, kéo chốt điều khiển điền xuống các ô mà bạn muốn áp dụng công thức này và tất cả các giá trị phù hợp đã được trả về như hình minh họa bên dưới:

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:

Chú thích: Trong công thức trên, C2 là giá trị tra cứu bạn muốn kiểm tra; A2: A10 là danh sách phạm vi mà từ đó các giá trị tra cứu sẽ được tìm thấy; con số 1 là số cột mà bạn muốn lấy giá trị trong phạm vi của mì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.

Vlookup và tính tổng tất cả các giá trị phù hợp trong một hàng hoặc nhiều hàng

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:

1. Vui lòng sao chép hoặc nhập công thức sau vào một ô trống, sau đó nhấn Ctrl + Shift + Enter các phím với nhau để có kết quả đầu tiên.

=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:

Chú thích: Trong công thức trên: H2 là ô chứa giá trị bạn đang tìm kiếm; A2: F9 là phạm vi dữ liệu (không có tiêu đề cột) bao gồm giá trị tra cứu và các giá trị phù hợp; Con số 2,3,4,5,6 {} là các số cột được sử dụng để tính tổng của phạm vi.

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)

Chú thích: Trong công thức trên: H2 là giá trị tra cứu bạn đang tìm kiếm; A2: A9 là tiêu đề hàng chứa giá trị tra cứu; B2: F9 phạm vi dữ liệu của các giá trị số mà bạn muốn tính tổng.

Vlookup và tính tổng tất cả các giá trị phù hợp trong một cột hoặc nhiều cột

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.

Áp dụng công thức dưới đây vào một ô trống, sau đó kéo chốt điền xuống để sao chép công thức này sang các ô khác, giờ đây, các giá trị khớp đầu tiên dựa trên tháng cụ thể trong một cột đã được tổng hợp lại với nhau, xem ảnh chụp màn hình:

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Chú thích: Trong công thức trên: H2 là giá trị tra cứu bạn đang tìm kiếm; B1: F1 là tiêu đề cột có chứa giá trị tra cứu; B2: F9 phạm vi dữ liệu của các giá trị số mà bạn muốn tính tổng.

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))

Chú thích: Trong công thức trên: H2 là giá trị tra cứu bạn đang tìm kiếm; B1: F1 là tiêu đề cột có chứa giá trị tra cứu; B2: F9 phạm vi dữ liệu của các giá trị số mà bạn muốn tính tổng.

Vlookup và tính tổng các giá trị phù hợp đầu tiên hoặc tất cả các giá trị phù hợp với một tính năng mạnh mẽ

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ờ!

Vlookup và tổng hợp tất cả các giá trị phù hợp cả trong hàng và cột

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 is tiê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.

Vlookup để hợp nhất hai bảng dựa trên một cột chính

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.

Công thức 1: Sử dụng hàm VLOOKUP

Để 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)

Chú thích: Trong công thức trên, A2 là giá trị bạn đang tìm kiếm, E2: F8 là bảng để tìm kiếm, số 2 là số cột trong bảng để lấy giá trị.

Công thức 2: Sử dụng hàm INDEX và MATCH

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.

Vui lòng sao chép hoặc nhập công thức dưới đây vào một ô trống, sau đó sao chép công thức xuống cột và cột thứ tự đã được nối với bảng đầu tiên, xem ảnh chụp màn hình:

=INDEX($E$2:$E$8, MATCH($A2, $F$2:$F$8, 0))

Chú thích: Trong công thức trên, A2 là giá trị tra cứu bạn đang tìm kiếm, E2: E8 là phạm vi dữ liệu mà bạn muốn trả lại, F2: F8 là phạm vi tra cứu chứa giá trị tra cứu.

Vlookup để hợp nhất hai bảng dựa trên nhiều cột chính

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

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. Vui lòng áp dụng công thức dưới đây vào một ô trống mà bạn muốn đặt kết quả, rồi nhấn Ctrl + Shift + Enter các phím với nhau để nhận giá trị phù hợp đầu tiên, xem ảnh chụp màn hình:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Chú thích: Trong công thức trên, những gì các tham chiếu ô thể hiện như ảnh chụp màn hình dưới đây:

2Sau đó, 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 2016 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.

: Trong Excel 2016 và các phiên bản mới hơn, bạn cũng có thể sử dụngtí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

Bạn đã bao giờ thử Vlookup các giá trị trên nhiều trang tính chưa? Giả sử tôi có ba trang tính sau với phạm vi dữ liệu và bây giờ, tôi muốn nhận một phần của các giá trị tương ứng dựa trên tiêu chí từ ba trang tính này để nhận được kết quả như hình minh họa bên dưới. Trong trường hợp này, Giá trị Vlookup trên nhiều trang tính hướng dẫn có thể giúp bạn từng bước một.

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

Như chúng ta đã biết, hàm Vlookup bình thường chỉ có thể giúp chúng ta trả về giá trị phù hợp từ một dải dữ liệu khác, nhưng đôi khi, bạn có thể muốn trả về giá trị tương ứng cùng với định dạng ô, chẳng hạn như màu tô, màu phông chữ, kiểu phông chữ như ảnh chụp màn hình dưới đây. Phần này sẽ nói về cách lấy định dạng ô với giá trị trả về trong Excel.

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)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                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

3. Vẫn trong Microsoft Visual Basic cho các ứng dụng cửa sổ, nhấp Chèn > Mô-đun, và sau đó sao chép mã VBA 2 bên dưới vào cửa sổ Mô-đun.

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 = ""
        xDic.Add Application.Caller.Address, ""
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
End Function

4. Sau khi chèn các mã trên, hãy nhấp vào CÔNG CỤ
> dự án trong Microsoft Visual Basic cho các ứng dụng cửa sổ. Sau đó kiểm tra Microsoft Script Runtime trong hộp kiểm Tài liệu tham khảo – VBAProject hộp thoại. Xem ảnh chụp màn hình:

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:

Chú thích: Trong công thức trên, E2 là giá trị bạn sẽ tra cứu, A1: C10 là phạm vi bảng và số 3 là số cột của bảng mà bạn muốn trả về giá trị phù hợp.

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

Thông thường, khi sử dụng chức năng Vloook để tra cứu và trả về giá trị định dạng ngày phù hợp, một số định dạng số sẽ được hiển thị như hình minh họa bên dưới. Để giữ định dạng ngày khỏi kết quả trả về, bạn nên đặt hàm TEXT vào hàm Vlookup.

Vui lòng áp dụng công thức dưới đây vào một ô trống, sau đó kéo chốt điền để sao chép công thức này sang các ô khác và tất cả các ngày phù hợp đã được trả về như hình minh họa bên dưới:

=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ổ.

2. Nhấp chuột Chèn > Mô-đun, sau đó sao chép và dán mã sau vào Cửa sổ mô-đun.

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

3. Sau đó lưu và đóng cửa sổ mã, nhập công thức này: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) vào một ô trống để tìm kết quả, sau đó kéo chốt điền để sao chép công thức này sang các ô khác, bây giờ, các giá trị phù hợp cũng như các nhận xét được trả lại cùng một lúc, xem ảnh chụp màn hình:

Chú thích: 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: B9 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ề.

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

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip