Thực đơn
trang chủHướng dẫn

Cú pháp hàm vlookup trong Excel. Thông tin cơ bản về chỉ mục và vị trí tìm kiếm. Chúng tôi sử dụng nhiều hàm VLOOKUPS trong một công thức

VLOOKUP là một hàm Excel cho phép bạn tìm kiếm một cột cụ thể bằng cách sử dụng dữ liệu trong cột khác. hàm VLOOKUP trong Excel nó còn được dùng để chuyển dữ liệu từ bảng này sang bảng khác. Có ba điều kiện:

  1. Các bảng phải được đặt trong một Sổ làm việc Excel.
  2. Bạn chỉ có thể tìm kiếm trong số dữ liệu tĩnh (không phải công thức).
  3. Cụm từ tìm kiếm phải nằm trong cột đầu tiên của dữ liệu được sử dụng.

Công thức VLOOKUP trong Excel

Cú pháp VLOOKUP trong Excel được Nga hóa, nó trông giống như:

VLOOKUP (tiêu chí tìm kiếm; phạm vi dữ liệu; số cột có kết quả; điều kiện tìm kiếm)

Các đối số cần thiết để tìm kiếm kết quả cuối cùng được chỉ định trong ngoặc đơn.

Tiêu chí tìm kiếm

Địa chỉ ô bảng tính Excel, trong đó chỉ định dữ liệu cần tìm kiếm trong bảng.

Phạm vi dữ liệu

Tất cả các địa chỉ trong đó việc tìm kiếm được thực hiện. Cột đầu tiên phải là cột chứa tiêu chí tìm kiếm.

Số cột cho tổng giá trị

Số cột mà giá trị sẽ được lấy nếu tìm thấy kết quả khớp.

Điều kiện tìm kiếm

Giá trị Boolean (true/1 hoặc false/0) chỉ định xem nên tìm kết quả khớp gần đúng (1) hay kết quả khớp chính xác (0).

VLOOKUP trong Excel: ví dụ về hàm

Nguyên lý hoạt động của chức năng rất đơn giản. Đối số đầu tiên chứa tiêu chí tìm kiếm. Ngay sau khi tìm thấy kết quả khớp trong bảng (đối số thứ hai), thông tin sẽ được lấy từ cột mong muốn (đối số thứ ba) của hàng tìm thấy và chèn vào ô có công thức.
Đơn giản ứng dụng VPR– tìm kiếm các giá trị trong bảng Excel. Nó có ý nghĩa trong khối lượng lớn dữ liệu.

Hãy tìm số lượng sản phẩm thực sự được phát hành theo tên của tháng.
Kết quả sẽ được hiển thị ở bên phải của bảng. Tại ô có địa chỉ H3 chúng ta sẽ nhập giá trị mong muốn. Trong ví dụ, tên của tháng sẽ được chỉ định ở đây.
Trong ô H4, chúng ta nhập chính hàm đó. Việc này có thể được thực hiện thủ công hoặc bạn có thể sử dụng trình hướng dẫn. Để gọi, đặt con trỏ lên ô H4 và nhấp vào biểu tượng Fx bên cạnh thanh công thức.


Một cửa sổ thuật sĩ sẽ mở ra Hàm Excel. Bạn cần tìm VLOOKUP trong đó. Chọn “Danh sách theo thứ tự bảng chữ cái đầy đủ” từ danh sách thả xuống và bắt đầu nhập VLOOKUP. Đánh dấu chức năng tìm thấy và nhấp vào OK.


Một cửa sổ VLOOKUP sẽ xuất hiện để bảng excel.


Để chỉ định đối số đầu tiên (tiêu chí), đặt con trỏ ở hàng đầu tiên và nhấp vào ô H3. Địa chỉ của cô ấy sẽ xuất hiện trong dòng. Để chọn một phạm vi, đặt con trỏ vào dòng thứ hai và bắt đầu chọn bằng chuột. Cửa sổ sẽ được thu nhỏ thành một dòng. Điều này được thực hiện để cửa sổ không cản trở khả năng nhìn toàn bộ phạm vi và không cản trở hành động của bạn.


Khi bạn đã chọn xong và thả nút trái chuột, cửa sổ sẽ trở về trạng thái bình thường và địa chỉ của dải ô sẽ xuất hiện ở dòng thứ hai. Nó được tính từ ô trên cùng bên trái đến ô dưới cùng bên phải. Địa chỉ của chúng được phân tách bằng toán tử “:” - tất cả địa chỉ giữa địa chỉ đầu tiên và địa chỉ cuối cùng đều được lấy.


Di chuyển con trỏ đến dòng thứ ba và đọc dữ liệu sẽ được lấy từ cột nào nếu tìm thấy kết quả khớp. Trong ví dụ của chúng tôi nó là 3.


Để trống dòng cuối cùng. Theo mặc định, giá trị sẽ là 1, hãy xem hàm của chúng ta sẽ xuất ra giá trị nào. Bấm vào đồng ý.


Kết quả là nản lòng. "Không áp dụng" có nghĩa là dữ liệu không hợp lệ cho chức năng. Chúng tôi chưa chỉ định giá trị trong ô H3 và hàm đang tìm kiếm giá trị trống.


Nhập tên của tháng và giá trị sẽ thay đổi.


Chỉ có điều nó không tương ứng với thực tế, vì số lượng sản phẩm thực tế sản xuất trong tháng 1 là năm 2000.
Đây là tác dụng của đối số Điều kiện tìm kiếm. Hãy thay đổi nó thành 0. Để thực hiện việc này, hãy đặt con trỏ lên ô có công thức và nhấn Fx lần nữa. Trong cửa sổ mở ra, nhập “0” vào dòng cuối cùng.


Nhấp vào "OK". Như bạn có thể thấy, kết quả đã thay đổi.


Để kiểm tra điều kiện thứ hai từ đầu bài viết (hàm không tìm kiếm trong các công thức), hãy thay đổi điều kiện của hàm. Hãy tăng phạm vi và cố gắng lấy giá trị từ một cột có các giá trị được tính toán. Nhập các giá trị như trong ảnh chụp màn hình.


Bấm vào đồng ý. Như bạn có thể thấy, kết quả tìm kiếm hóa ra là 0, mặc dù giá trị trong bảng là 85%.


VLOOKUP trong Excel chỉ “hiểu” giá trị cố định.

So sánh dữ liệu từ hai bảng Excel

VLOOKUP trong Excel có thể được sử dụng để so sánh dữ liệu từ hai bảng. Ví dụ: giả sử chúng ta có hai trang chứa dữ liệu về sản phẩm đầu ra từ hai xưởng. Chúng ta có thể sánh đôi phát hành thực tế cho cả hai. Hãy để chúng tôi nhắc bạn rằng để chuyển đổi giữa các trang tính, hãy sử dụng các phím tắt của chúng ở cuối cửa sổ.

Trên hai trang tính, chúng ta có các bảng giống hệt nhau với dữ liệu khác nhau.

Như bạn có thể thấy, kế hoạch phát hành của họ giống nhau, nhưng kế hoạch thực tế lại khác. Chuyển đổi và so sánh từng dòng ngay cả đối với khối lượng nhỏ dữ liệu rất bất tiện. Trên trang thứ ba, tạo một bảng có ba cột.

Trong ô B2, chúng ta nhập hàm VLOOKUP. Đối số đầu tiên, chúng ta sẽ chỉ ra ô có tháng trên trang tính hiện tại và chọn phạm vi từ trang tính “Workshop1”. Để ngăn phạm vi dịch chuyển khi sao chép, hãy nhấn F4 sau khi chọn phạm vi. Điều này sẽ làm cho liên kết trở nên tuyệt đối.


Mở rộng công thức để bao gồm toàn bộ cột.

Tương tự, nhập công thức vào cột tiếp theo, chỉ chọn phạm vi trên bảng “Workshop2”.


Sau khi sao chép, bạn sẽ nhận được báo cáo tóm tắt từ hai tờ.

Thay thế dữ liệu từ bảng Excel này sang bảng Excel khác

Hành động này được thực hiện theo cách tương tự. Trong ví dụ của chúng tôi, bạn không cần tạo bàn riêng, mà chỉ cần nhập hàm vào một cột của bất kỳ bảng nào. Hãy hiển thị nó bằng ví dụ đầu tiên. Đặt con trỏ vào cột cuối cùng.


Và trong ô G3 đặt hàm VLOOKUP. Chúng tôi lại lấy phạm vi từ bảng liền kề.


Kết quả là cột của bảng thứ hai sẽ được sao chép sang cột đầu tiên.


Đó là tất cả thông tin về cái vô hình, nhưng chức năng hữu ích VLOOKUP trong Excel dành cho người mới bắt đầu. Chúng tôi hy vọng nó sẽ giúp bạn giải quyết vấn đề.

Có một ngày tuyệt vời!

Nhiều người biết đến và sử dụng thường xuyên. Nhưng đồng thời, nó có hai nhược điểm đáng kể, chẳng hạn như cách tạo “VLOOKUP bên trái”. Tôi chỉ sử dụng VLOOKUP nếu tôi cần làm điều gì đó nhanh chóng. Trong các tệp để sử dụng “thông thường”, tôi tạo cấu trúc INDEX và TÌM KIẾM. Làm thế nào là nó tốt hơn?

  1. Khi thêm cột vào bảng dữ liệu, bạn không cần thay đổi số cột trong chính công thức (như trong hàm VLOOKUP). Cột sẽ tự động di chuyển
  2. Bạn có thể thực hiện VLOOKUP theo cách khác, tức là thực hiện lựa chọn từ bảng từ phải sang trái. Trong VLOOKUP, cột đầu tiên phải luôn có thể tìm kiếm được.

Làm thế nào để làm tất cả điều này, đọc bên dưới :)

Tôi sẽ sử dụng một ví dụ từ. Để hiểu rõ hơn về ứng dụng của thiết kế này so với VLOOKUP:

Việc điền công thức có dễ dàng không nếu bạn nhìn thấy công thức này lần đầu tiên?

Đầu tiên, hãy quyết định vị trí của bảng ban đầu và nơi nhận dữ liệu! Từng bước điền những gì.

  1. Chèn công thức vào vùng mong muốn của bảng
  2. Thay cho $G:$G, hãy đặt các ô cần tìm thấy giá trị và theo đó, kết quả sẽ xuất hiện. Chúng tôi đang tìm họ, vì vậy hãy tìm cột có họ trong bảng ban đầu.
  3. Thay thế $J:$J bằng tùy thuộc vào giá trị nào sẽ được trả về ô. Chúng tôi cần Họ tùy thuộc vào loại xe - hãy chèn những loại xe đó bên cạnh giá trị sẽ xuất hiện.
  4. Thay vì $H:$H, hãy điền vào cột mà bạn cần tìm giá trị tương ứng. Những thứ kia. Chúng tôi đang tìm Họ theo Xe, có nghĩa là chúng tôi chèn một cột có Xe vào bảng ban đầu.

Nếu bạn đang thắc mắc dấu $ dùng để làm gì trong công thức, hãy đọc

CHỈ SỐ và TÌM KIẾM. Những chức năng này là gì?

INDEX và MATCH rất Tính năng mạnh mẽ, kết hợp với những thứ khác sẽ mang lại kết quả tuyệt vời.

INDEX(mảng; số_hàng; số_cột)

Trả về giá trị tại giao điểm của một hàng và cột được chỉ định trong một phạm vi đã chỉ định. Những thứ kia. ban đầu nó hoạt động với mảng hai chiều.

Bằng cách chọn mảng dữ liệu J1:K4 và đặt số hàng và số cột thành 2, chúng ta thu được giá trị tương ứng.

Nhìn vào công thức ban đầu

INDEX($G:$G,MATCH($J:$J,$H:$H,0),1)

Chúng ta sẽ thấy rằng thay vì đối số thứ hai (số dòng), chúng ta có công thức MATCH. Cô ấy đang làm gì ở đây?

MATCH là tìm kiếm theo giá trị. Tìm kiếm chức năng đặt giá trị trong một hàng hoặc cột và trả về số thứ tự của nó (từ đầu phạm vi). Những thứ kia. trong đối số thứ hai của hàm INDEX, chúng ta tìm số xe mình cần, chúng ta lấy số của nó, ví dụ 2.

Và trong mảng một chiều $G:$G, chúng ta tìm thấy một ô có số hàng = 2. Điều này sẽ áp dụng cho từng ô trong cột J.

Vì vậy, có vẻ như đây không phải là một thiết kế quá phức tạp nhưng như tôi đã viết ở trên, nó rất hiệu quả. Bởi vì bạn không cần phải liên tục thay đổi số lượng giá trị mong muốn, như trong VLOOKUP và bạn có thể tìm kiếm cả bên phải và bên trái :)

Viết bình luận nếu bạn có bất kỳ câu hỏi.

Như mọi khi!

Chia sẻ bài viết của chúng tôi trên mạng xã hội của bạn:

Tất cả chúng ta đều biết nó hữu ích như thế nào. Có lẽ một nửa số hành động trong Excel được thực hiện bằng cách sử dụng nó. Tuy nhiên Chức năng này có một số hạn chế. Ví dụ: hàm VLOOKUP chỉ xem xét cột ngoài cùng bên trái trong bảng hoặc chỉ tìm kiếm một điều kiện. Nhưng nếu chúng ta cần trả về một giá trị thỏa mãn hai điều kiện thì sao? Trong trường hợp này, chúng ta sẽ phải dùng đến một số thủ thuật. Chúng ta sẽ nói về những thủ thuật này trong bài viết hôm nay.

Vì vậy, chúng ta sẽ xem xét bốn tùy chọn để tạo hàm thay thế với hai điều kiện:

  1. Sử dụng hàm SUMPRODVEL

Chà, chúng ta sẽ bắt đầu với điều đơn giản nhất.

Sử dụng cột bổ sung

Trong hầu hết các trường hợp vấn đề phức tạp trở nên dễ dàng và dễ quản lý hơn khi được chia thành nhiều phần nhỏ. Điều tương tự cũng áp dụng khi xây dựng công thức trong Excel.

Hãy xem một ví dụ cổ điển. Chúng tôi có một bảng với doanh số bán hàng theo tháng và thành phố. Và chúng ta cần xác định giá trị bán hàng tương ứng với hai điều kiện: tháng - Tháng 2 và thành phố - Samara.

Sử dụng hàm VLOOKUP trong hình thức cổ điển sẽ không giúp ích gì cho chúng ta vì nó sẽ có thể trả về một giá trị chỉ khớp với một điều kiện. Một cột bổ sung sẽ giúp chúng ta thoát khỏi tình trạng này, trong đó chúng ta sẽ kết hợp các giá trị của cột Tháng và Thành phố. Để thực hiện việc này, hãy viết công thức =B2&C2 vào ô A2 và mở rộng công thức này sang ô A13. Bây giờ chúng ta có thể sử dụng các giá trị của cột A để trả về giá trị được yêu cầu. Chúng ta viết công thức ở ô G3:

VLOOKUP(G1A2:D13;4;0)

Công thức này kết hợp hai điều kiện của ô G1 và G2 thành một hàng và xem xét nó trong cột A. Sau Điều kiện cần thiếtđược tìm thấy, công thức trả về giá trị từ cột thứ tư của bảng A1:D13, tức là. cột Việc bán hàng.

Sử dụng hàm SELECT để tạo bảng tra cứu mới

Nếu vì lý do nào đó việc sử dụng cột bổ sung không phải là một lựa chọn đối với chúng tôi, chúng tôi có thể sử dụng .

Sử dụng chức năng SELECT liên quan đến việc tạo bảng mớiđể xem các giá trị cột ở đâu Tháng Và Thành phốđã sáp nhập rồi. Công thức của chúng tôi sẽ trông như thế này:

VLOOKUP(G1SELECT((1,2),B2:B13&C2:C13,D2:D13),2,0)

Điểm chính của công thức này là phần CHOICE((1;2);B2:B13&C2:C13;D2:D13), thực hiện hai việc:

  1. Kết hợp các giá trị cột Tháng Và Thành phố trong một mảng: JanMoscow, FebMoscow...
  2. Kết hợp hai mảng thành một bảng hai cột.

Kết quả của hàm này sẽ là một bảng trông như thế này:

Bây giờ công thức đã trở nên rõ ràng hơn.

QUAN TRỌNG: Vì chúng ta đã sử dụng công thức mảng nên khi bạn nhập xong công thức, hãy nhấn Ctrl+Shift+Enter để cho chương trình biết ý định của chúng ta. Sau khi nhấn tổ hợp phím này, chương trình sẽ tự động cài đặt dấu ngoặc nhọn ở đầu và cuối công thức.

Sử dụng hàm INDEX và MATCH

Phương pháp thứ ba mà chúng ta sẽ xem xét cũng liên quan đến việc sử dụng công thức mảng và sử dụng các hàm INDEX và SEARCH.

Công thức sẽ trông như thế này.

CHỈ SỐ(D2:D13,MATCH(1,(B2:B13=G1)*(C2:C13=G2),0))

Chúng ta hãy xem từng phần của công thức này có tác dụng gì.

Đầu tiên, hãy xem xét hàm MATCH(1;(B2:B13=G1)*(C2:C13=G2);0). TRONG trong trường hợp này tuần tự so sánh giá trị của ô G1 với từng giá trị của ô trong phạm vi B2:B13 và trả về TRUE nếu các giá trị khớp và FALSE nếu không khớp. Sự so sánh tương tự được thực hiện với giá trị của ô G2 và phạm vi C2:C13. Tiếp theo chúng ta so sánh cả hai mảng này bao gồm TRUE và FALSE. Tổ hợp TRUE * TRUE cho ta kết quả 1 (TRUE). Chúng ta hãy nhìn vào hình ảnh dưới đây sẽ giúp giải thích nguyên lý hoạt động rõ ràng hơn.

Bây giờ chúng ta có thể biết chuỗi thỏa mãn cả hai điều kiện nằm ở đâu. Hàm MATCH tìm vị trí của số 1 trong mảng kết quả và trả về 6 vì số 1 xuất hiện ở hàng thứ sáu. Tiếp theo, hàm INDEX trả về giá trị của hàng thứ sáu của phạm vi D2:D13.

Sử dụng TÓM TẮT

Một trong những người mạnh mẽ nhất Công thức Excel. Tôi thậm chí còn có một bài viết riêng dành riêng cho công thức này. Cách thứ tư để sử dụng nhiều điều kiện là viết công thức bằng hàm SUMPRODVEL. Và nó sẽ trông như thế này:

TỔNG HỢP((B2:B13=G1)*(C2:C13=G2);D2:D13)

Nguyên lý hoạt động của công thức này tương tự như nguyên lý hoạt động của phương pháp trước. Tạo bảng ảo, so sánh giá trị của các ô G1 và G2 với các phạm vi B2:B13 và C2:C13 tương ứng. Tiếp theo, cả hai mảng này được so sánh và thu được một mảng gồm các số 1 và 0, trong đó một mảng được gán cho hàng trong đó cả hai điều kiện đều khớp nhau. Tiếp theo, mảng ảo này được nhân với phạm vi D2:D13. Vì mảng ảo của chúng ta sẽ chỉ có một số 1 ở hàng thứ sáu nên công thức sẽ trả về kết quả 189.

Chức năng này sẽ không hoạt động nếu có giá trị văn bản.

Để hiểu nó hoạt động như thế nào công thức này, Tôi khuyên bạn nên đọc bài viết về hàm SUMPROIZ.

KẾT QUẢ

Vậy bạn nên sử dụng phương pháp nào? Mặc dù tất cả chúng đều hoạt động đáng tin cậy nhưng tôi thích phương pháp đầu tiên hơn. Trong của anh ấy công việc hàng ngày, Tôi thích làm việc với các tệp dễ hiểu và có thể thay đổi. Cả hai yêu cầu này đều đáp ứng các điều kiện của phương pháp đầu tiên.

Để hiểu rõ hơn về các công thức, bạn có thể xem chúng trong bài viết hôm nay.

Làm việc với bảng tóm tắt liên quan đến việc kéo các giá trị từ các bảng khác vào đó. Nếu có nhiều bàn thì việc chuyển thủ công sẽ mất số lượng lớn thời gian và nếu dữ liệu được cập nhật liên tục thì đây sẽ là nhiệm vụ của Sisyphean. May mắn thay, có một hàm VLOOKUP cung cấp khả năng tự động tìm nạp dữ liệu. hãy xem xét ví dụ cụ thể hoạt động của chức năng này.

Tên của hàm VLOOKUP là viết tắt của “chức năng xem dọc”. Trong tiếng Anh tên của nó là VLOOKUP. Hàm này tìm kiếm dữ liệu ở cột bên trái của phạm vi đang được kiểm tra và sau đó trả về giá trị kết quả trong ô đã chỉ định. Nói một cách đơn giản, VLOOKUP cho phép bạn sắp xếp lại các giá trị từ một ô trong bảng này sang bảng khác. Hãy cùng tìm hiểu cách sử dụng hàm VLOOKUP trong Excel nhé.

Ví dụ về sử dụng VLOOKUP

Chúng ta hãy xem cách hàm VLOOKUP hoạt động bằng một ví dụ cụ thể.

Chúng tôi có hai bảng. Đầu tiên trong số đó là một bảng mua hàng, trong đó đặt tên các sản phẩm thực phẩm. Cột tiếp theo sau tên là giá trị số lượng sản phẩm cần mua. Tiếp theo là giá cả. Và ở cột cuối cùng - tổng chi phí mua một sản phẩm cụ thể, được tính bằng công thức nhân số lượng với giá đã được nhập vào ô. Nhưng chúng ta sẽ chỉ phải tăng giá bằng cách sử dụng hàm VLOOKUP từ bảng bên cạnh, đây là bảng giá.


Như bạn có thể thấy, giá khoai tây đã được thêm vào bảng từ bảng giá. Để không phải thực hiện thủ tục phức tạp như vậy với tên sản phẩm khác, chúng ta chỉ cần đứng ở góc dưới bên phải của ô đã điền để xuất hiện hình chữ thập. Chúng tôi vẽ chữ thập này ở cuối bảng.

Do đó, chúng tôi đã lấy tất cả dữ liệu cần thiết từ bảng này sang bảng khác bằng hàm VLOOKUP.

Như bạn có thể thấy, hàm VLOOKUP không phức tạp như thoạt nhìn. Tìm hiểu công dụng của nó không khó lắm nhưng việc thành thạo công cụ này sẽ giúp bạn tiết kiệm rất nhiều thời gian khi làm việc với bảng.

Định dạng có điều kiện (5)
Danh sách và phạm vi (5)
Macro (thủ tục VBA) (63)
Khác (39)
Lỗi và trục trặc của Excel (3)

Cách tìm giá trị trong bảng khác hoặc cường độ VLOOKUP

Trên thực tế, trong bài viết này tôi muốn nói về những khả năng không chỉ Hàm VLOOKUP, nhưng tôi cũng muốn chạm vào TÌM KIẾM, như một chức năng rất liên quan đến VPR. Mỗi chức năng này đều có cả ưu và nhược điểm. Tóm lại, VLOOKUP tìm kiếm một giá trị nhất định mà chúng ta chỉ định trong số nhiều giá trị nằm trong một cột. Có lẽ nhu cầu VLOOKUP thường xuất hiện nhất khi bạn cần so sánh dữ liệu, tìm dữ liệu trong bảng khác, thêm dữ liệu từ bảng này sang bảng khác, dựa trên một số tiêu chí, v.v.
Để hiểu nguyên tắc tốt hơn một chút công việc VPR tốt hơn nên bắt đầu với cái gì đó ví dụ thực tế. Có một bảng như thế này:
Hình 1

và từ bảng đầu tiên phải thay thế vào ngày thứ hai cho mỗi họ. Đối với ba bản ghi, đây không phải là vấn đề và việc thực hiện thủ công là điều hiển nhiên. Nhưng trong đời thực, đây là những bảng có hàng nghìn bản ghi và việc tìm kiếm bằng cách thay thế dữ liệu theo cách thủ công có thể mất hơn một giờ. Cộng thêm một vài điều nữa trong thuốc mỡ: không chỉ những cái tên nằm hoàn toàn trong theo thứ tự khác nhau Trong cả hai bảng, số lượng bản ghi trong bảng là khác nhau và các bảng nằm trên các trang/sách khác nhau. Tôi tin rằng tôi đã thuyết phục bạn rằng việc thay thế thủ công hoàn toàn không phải là một lựa chọn. Nhưng VLOOKUP sẽ không thể thiếu ở đây. Trong trường hợp này, thực tế không cần phải làm gì - chỉ cần viết vào ô đầu tiên của cột C của bảng thứ hai (nơi bạn cần thay thế ngày từ bảng đầu tiên) công thức này:
=VLOOKUP($A2 ;Sheet1!$A$2:$C$4 ;3,0)
Bạn có thể viết công thức trực tiếp vào ô hoặc sử dụng trình quản lý hàm, chọn trong danh mục Liên kết và mảng VPR và chỉ ra riêng các tiêu chí cần thiết. Bây giờ chúng tôi sao chép ( Điều khiển+C) có công thức, chọn tất cả các ô trong cột C vào cuối dữ liệu rồi chèn ( Điều khiển+V.).

Đầu tiên, nguyên tắc hoạt động cơ bản: VLOOKUP tìm kiếm trong cột đầu tiên của đối số Bảng để tìm giá trị được đối số chỉ định Tìm kiếm_giá trị . Khi tìm thấy giá trị mong muốn, hàm trả về giá trị đối diện với giá trị tìm thấy, nhưng từ cột được chỉ định bởi đối số Cột_số . Chúng ta sẽ giải quyết vấn đề xem tua nhanh thời gian sau. VLOOKUP chỉ có thể trả về một giá trị - giá trị đầu tiên phù hợp với tiêu chí. Nếu không tìm thấy giá trị bạn đang tìm kiếm (không có trong bảng), thì kết quả của hàm sẽ là #Không áp dụng . Không cần phải sợ điều này - nó thậm chí còn hữu ích. Bạn sẽ biết chính xác bản ghi nào bị thiếu và từ đó có thể so sánh hai bảng với nhau. Đôi khi bạn thấy: có dữ liệu trong cả hai bảng, nhưng hàm VLOOKUP tạo ra #N/A. Điều này có nghĩa là dữ liệu trong bảng của bạn không giống nhau. Một số trong số chúng có khoảng trắng quá khó thấy (thường là trước hoặc sau giá trị) hoặc các ký tự Cyrillic được trộn với các ký tự Latinh. Cũng #Không áp dụng sẽ như thế nào nếu tiêu chí là số và trong bảng mong muốn, chúng được viết dưới dạng văn bản (thường ở bên trái góc trên cùng một ô như vậy sẽ xuất hiện một hình tam giác màu xanh lá cây), và trong cái cuối cùng - dưới dạng số. Hoặc ngược lại.

Sự miêu tả đối số VLOOKUP
$A2 - đối số Tìm kiếm_giá trị(hãy gọi nó là Tiêu chuẩn trở nên ngắn). Đây là những gì chúng tôi đang tìm kiếm. Những thứ kia. đối với kỷ lục đầu tiên của bảng thứ hai sẽ là Petrov S.A. Tại đây, bạn có thể chỉ định trực tiếp văn bản của tiêu chí (trong trường hợp này nó phải ở trong dấu ngoặc kép - =VLOOKUP("Petrov S.A" ;Sheet1!$A$2:$C$4;3;0) hoặc một liên kết đến một ô có văn bản này (như trong ví dụ về hàm). Có một sắc thái nhỏ: bạn cũng có thể sử dụng các ký tự đại diện: "*" và "?". Điều này rất thuận tiện nếu bạn chỉ cần tìm giá trị cho một phần của chuỗi. Ví dụ: bạn không thể nhập đầy đủ “Petrov S.A” mà chỉ nhập họ và dấu hoa thị - “Petrov*”. Sau đó, bất kỳ mục nào bắt đầu bằng “Petrov” sẽ được hiển thị. Nếu bạn cần tìm một bản ghi trong đó họ “Petrov” xuất hiện ở bất kỳ đâu trên dòng, thì bạn có thể chỉ định bản ghi đó như sau: “*Petrov*”. Nếu bạn muốn tìm họ Petrov và không quan trọng tên viết tắt và tên viết tắt sẽ có những gì (nếu tên đầy đủ được viết là Ivanov I.I.), thì kiểu nhìn này là vừa phải: “Ivanov?.?.” . Thường thì cần phải chỉ ra giá trị của nó cho từng dòng (trong cột A Họ và bạn cần tìm tất cả). Trong trường hợp này, các tham chiếu đến các ô của cột A. Ví dụ: trong ô A1 nó được viết: Ivanov. Người ta cũng biết rằng Ivanov nằm trong một bảng khác, nhưng sau họ, cả tên và tên đệm (hoặc tên gì khác) đều có thể được viết ra. Nhưng chúng ta chỉ cần tìm chuỗi bắt đầu bằng họ. Sau đó, bạn cần viết nó như sau: A1 &"*" . Mục này sẽ tương đương với "Ivanov*". A1 được viết là Ivanov, dấu và(&) dùng để kết hợp hai giá trị văn bản thành một chuỗi. Dấu hoa thị nằm trong dấu ngoặc kép (như văn bản bên trong công thức). Vì vậy, chúng tôi nhận được:
A1&"*" =>
"Ivanov"&"*" =>
"Ivanov*"
Rất thuận tiện nếu có nhiều giá trị cần tìm kiếm.
Nếu bạn cần xác định xem có một từ nào đó ở đâu đó trong một dòng hay không thì hãy đặt dấu hoa thị ở cả hai bên: "*"& A1 &"*"

Sheet1!$A$2:$C$4 - đối số Bàn. Chỉ định phạm vi của các ô. Chỉ phạm vi phải chứa dữ liệu từ ô dữ liệu đầu tiên đến ô cuối cùng. Đây không nhất thiết phải là phạm vi được hiển thị trong ví dụ. Nếu có 100 hàng thì Sheet1!$A$2:$C$100. Điều quan trọng cần nhớ là ba điều: thứ nhất, điều này Bàn phải luôn bắt đầu bằng cột mà chúng ta đang tìm kiếm Tiêu chuẩn . Và không có gì khác. Nếu không, sẽ không tìm thấy gì hoặc kết quả sẽ không như bạn mong đợi. Thứ hai: lập luận Bàn phải "sửa" . Nó có nghĩa là gì. Bạn có thấy ký hiệu đô la không? Đây là sự hợp nhất (chính xác hơn là nó được gọi là tham chiếu tuyệt đối mỗi phạm vi). Nó được thực hiện như thế nào. Đánh dấu văn bản liên kết (chỉ một phạm vi - một tiêu chí) và hãy nhấn F4 cho đến khi bạn thấy đô la đó xuất hiện trước cả tên cột và số hàng. Nếu điều này không được thực hiện thì khi sao chép công thức, đối số Bảng sẽ “di chuyển ra ngoài” và kết quả sẽ lại sai. Và cuối cùng, bảng phải chứa các cột từ cột đầu tiên (mà chúng ta đang tìm kiếm) đến cột cuối cùng (từ đó chúng ta cần trả về giá trị). Trong ví dụ Trang 1!$A$2:$C$4- điều này có nghĩa là sẽ không thể trả về giá trị từ cột D(4), bởi vì bảng chỉ có ba cột.

3 - Cột_số. Ở đây chúng ta chỉ đơn giản chỉ ra số cột trong đối số Bàn, các giá trị mà chúng ta cần thay thế làm kết quả. Trong ví dụ, đây là Ngày chấp nhận - tức là. cột số 3. Nếu cần một bộ phận thì chúng ta sẽ chỉ ra 2 và nếu chúng ta chỉ cần so sánh xem có tên từ bảng này trong bảng khác hay không thì chúng ta có thể chỉ định 1. Quan trọng: đối số Cột_số không được vượt quá số cột trong đối số Bàn . Ngược lại công thức sẽ bị lỗi #LIÊN KẾT!. Ví dụ: nếu phạm vi $B$2:$C$4 được chỉ định và bạn cần trả về dữ liệu từ cột C, thì việc chỉ định 2 là đúng. Bởi vì lý lẽ Bàn($B$2:$C$4) chỉ chứa hai cột - B và C. Nếu bạn cố gắng chỉ định số cột 3 (như xuất hiện trên trang tính), bạn sẽ gặp lỗi #LIÊN KẾT!, bởi vì đơn giản là không có cột thứ ba trong phạm vi được chỉ định.

Lời khuyên thực tế: nếu đối số Bảng có quá nhiều cột và bạn cần trả về kết quả từ cột cuối cùng thì không cần thiết phải tính số lượng của chúng. Bạn có thể chỉ định nó như thế này: =VLOOKUP($A2 ;Sheet1! $A$2:$C$4 ;COLUMN NUM(Sheet1! $A$2:$C$4);0) . Nhân tiện, trong trường hợp này là Sheet1! cũng có thể bị xóa nếu không cần thiết: ​​=VLOOKUP($A2 ;Sheet1! $A$2:$C$4 ;COLUMN NUM($A$2:$C$4);0) .

0 - Thời gian trôi đi_view- một lập luận rất thú vị. Có thể là TRUE hoặc FALSE. Câu hỏi ngay lập tức được đặt ra: tại sao lại có số 0 trong công thức của tôi? Rất đơn giản - Excel trong công thức có thể coi 0 là FALSE và 1 là TRUE. Nếu bạn chỉ định trong VLOOKUP thông số này bằng 0 hoặc FALSE thì kết quả khớp chính xác sẽ được tìm kiếm tiêu chí nhất định. Điều này không liên quan gì đến các ký tự đại diện ("*" và "?"). Nếu bạn sử dụng 1 hoặc TRUE (hoặc không chỉ định đối số cuối cùng, vì theo mặc định nó là TRUE), thì... Đó là một câu chuyện rất dài. Nói tóm lại - VLOOKUP sẽ tìm giá trị tương tự nhất phù hợp Tiêu chuẩn . Đôi khi rất hữu ích. Tuy nhiên, nếu bạn sử dụng tham số này thì danh sách trong đối số Table cần phải được sắp xếp theo thứ tự tăng dần. Xin lưu ý rằng việc sắp xếp chỉ cần thiết nếu đối số Interval_lookup là TRUE hoặc 1. Nếu nó là 0 hoặc FALSE thì không cần sắp xếp.

Nhiều người có lẽ đã nhận thấy rằng trong hình tôi có sự xáo trộn giữa các bộ phận dành cho tên đầy đủ. Đây không phải là lỗi ghi âm. Ví dụ đính kèm bài viết cho thấy cách bạn có thể thay thế cả chúng và ngày tháng bằng một công thức mà không cần thay đổi đối số theo cách thủ công Cột_số. Đối với tôi, có vẻ như một ví dụ như vậy có thể khá hữu ích.

Làm cách nào để tránh lỗi #N/A (#N/A) trong VLOOKUP?
Hơn vấn đề thường gặp- nhiều người không muốn thấy kết quả là #N/A nếu không tìm thấy kết quả phù hợp. Điều này rất dễ dàng để đi lại:
=IF(END(VLOOKUP($A2,Sheet1! $A$2:$C$4,3,0));"";VLOOKUP($A2,Sheet1! $A$2:$C$4,3,0)))
Bây giờ nếu VLOOKUP không tìm thấy kết quả khớp, ô sẽ trống.
Và tới người dùng Các phiên bản Excel 2007 trở lên bạn có thể sử dụng IFERROR:
=IFERROR(VLOOKUP($A2,Sheet1! $A$2:$C$4,3,0);"")

Trận đấu hứa hẹn

Hàm này tìm kiếm giá trị được chỉ định bởi tham số Tìm kiếm_giá trị trong cuộc tranh luận Xem_mảng. Và kết quả của hàm là số vị trí của giá trị tìm được trong Xem_mảng. Đó là số vị trí, không phải giá trị. Về nguyên tắc, tôi sẽ không mô tả nó một cách chi tiết vì những điểm chính hoàn toàn giống nhau. Nếu chúng ta muốn áp dụng nó vào bảng trên, nó sẽ như thế này:
=MATCH($A2,Trang1! $A$2:$A$4,0)
$A2 - Giá trị tìm kiếm. Ở đây mọi thứ hoàn toàn giống với VLOOKUP. Các ký tự đại diện cũng được cho phép và có thiết kế giống hệt nhau.

Trang 1! $A$2:$A$4 - Mảng cần xem. Sự khác biệt chính so với VLOOKUP là bạn có thể chỉ định một mảng chỉ có một cột. Đây phải là cột chúng ta sẽ tìm kiếm Tìm kiếm_giá trị . Nếu bạn cố gắng chỉ định nhiều hơn một cột, hàm sẽ trả về lỗi.

match_type(0) - giống như trong VPR Thời gian trôi đi_view . Với các tính năng tương tự. Nó chỉ khác ở khả năng tìm kiếm cái nhỏ nhất từ ​​cái mong muốn hoặc cái lớn nhất. Nhưng tôi sẽ không tập trung vào điều này trong bài viết này.

Chúng tôi đã sắp xếp những điều cơ bản. Nhưng chúng ta cần trả về không phải số vị trí mà là giá trị của chính nó. Vì vậy MATCH trong thể tinh khiết không phù hợp với chúng tôi Ít nhất một, bởi chính nó. Nhưng nếu nó được sử dụng cùng với hàm INDEX thì đây chính là thứ chúng ta cần và thậm chí còn hơn thế nữa.
=INDEX(Sheet1! $A$2:$C$4 ;MATCH($A2 ;Sheet1! $A$2:$A$4 ;0);2)
Công thức này sẽ trả về kết quả tương tự như VLOOKUP.

Đối số hàm INDEX
Trang 1! $A$2:$C$4 - Mảng. Với đối số này, chúng tôi chỉ định phạm vi mà chúng tôi muốn nhận các giá trị. Có thể có một cột hoặc nhiều cột. Nếu chỉ có một cột thì không cần chỉ định đối số hàm cuối cùng. Nhân tiện, đối số này có thể không trùng khớp chút nào với đối số chúng ta chỉ định trong đối số Lookup_array của hàm MATCH.

Tiếp theo là Row_Number và Column_Number. Chúng ta thay thế Row_Number bằng MATCH, số này trả về cho chúng ta số vị trí trong mảng. Đây là những gì mọi thứ được xây dựng trên. INDEX trả về giá trị từ Mảng nằm trong hàng (RowNumber) được chỉ định của Mảng và cột được chỉ định (ColumnNumber) nếu có nhiều hơn một cột. Điều quan trọng cần biết là trong sự kết hợp này, số hàng trong đối số Mảng của hàm INDEX và số hàng trong đối số Lookup_array của hàm MATCH phải khớp nhau. Và bắt đầu từ cùng một dòng. Đây là trường hợp thông thường, trừ khi bạn đang theo đuổi các mục tiêu khác.
Giống như VLOOKUP, INDEX trả về #N/A nếu không tìm thấy giá trị mong muốn. Và bạn có thể khắc phục những lỗi như vậy theo cách tương tự:
Đối với tất cả các phiên bản Excel (bao gồm 2003 trở về trước):
=IF(END(MATCH($A2,Sheet1! $A$2:$A$4,0));"";INDEX(Sheet1! $A$2:$C$4;MATCH($A2,Sheet1! $A$2: $A$4 ;0);2))
Đối với phiên bản 2007 trở lên:
=IFERROR(INDEX(Sheet1! $A$2:$C$4,MATCH($A2,Sheet1!$A$2:$A$4,0),2);"")

Làm việc với tiêu chí dài hơn 255 ký tự
TÌM KIẾM CHỈ SỐ còn có một lợi thế nữa so với VLOOKUP. Vấn đề là VLOOKUP không thể tra cứu giá trị độ dài dòng chứa hơn 255 ký tự. Điều này hiếm khi xảy ra, nhưng nó vẫn xảy ra. Tất nhiên, bạn có thể gian lận VLOOKUP và cắt giảm tiêu chí:
=VLOOKUP(PSTR($A2,1,255),PSTR(Sheet1!$A$2:$C$4,1,255),3,0)
nhưng đây là công thức mảng. Và bên cạnh đó, công thức như vậy không phải lúc nào cũng trả về kết quả như ý. Nếu 255 ký tự đầu tiên giống với 255 ký tự đầu tiên trong bảng, sau đó các ký tự khác nhau thì công thức sẽ không còn thấy điều này nữa. Và công thức chỉ trả về các giá trị văn bản, điều này không thuận tiện lắm trong trường hợp cần trả về số.

Vì vậy, tốt hơn là sử dụng công thức phức tạp này:
=INDEX(Sheet1!$A$2:$C$4,SUMPRODVEL(MATCH(TRUE,Sheet1!$A$2:$A$4 =$A2,0));2)
Ở đây tôi đã sử dụng các phạm vi tương tự trong các công thức để dễ đọc, nhưng trong ví dụ về tải xuống, chúng khác với các phạm vi được chỉ ra ở đây.
Bản thân công thức này được xây dựng dựa trên khả năng của hàm SUMPRODVEL có thể chuyển đổi thành các phép tính lớn của một số hàm bên trong nó. Trong trường hợp này, MATCH tìm vị trí hàng trong đó tiêu chí bằng giá trị trong hàng. Các ký tự đại diện không còn có thể được sử dụng ở đây nữa.

Trong ví dụ đính kèm với bài viết, bạn sẽ tìm thấy các ví dụ về cách sử dụng tất cả các trường hợp được mô tả và ví dụ về lý do tại sao INDEX và MATCH đôi khi được ưa chuộng hơn VLOOKUP.

Tải xuống ví dụ

(26,0 KiB, 13.776 lượt tải xuống)

Bài viết có giúp ích gì không? Chia sẻ liên kết với bạn bè của bạn! Video bài học

("Thanh dưới cùng":("textstyle://static","textpositionstatic://bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic://bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect://slide","texteffecteasing":easeOutCubic","texteffectduration":600,"texteffectlidedirection">left","texteffectlidedistance" :30,"texteffectdelay":500,"texteffecteparate":false,"texteffect1"slide","texteffectlidedirection1""right","texteffectlidedistance1":120,"texteffecteasing1"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectlidedirection2""right","texteffectlidedistance2":120,"texteffecteasing2"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss://display:block; đệm:12px; text-align:left;","textbgcss">display:block; vị trí:tuyệt đối; top:0px; left:0px; chiều rộng:100%; chiều cao:100% ; màu nền:#333333; độ mờ:0.6; bộ lọc:alpha(opacity=60);","titlecss://display:block; vị trí: tương đối; phông chữ:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss">display:block; vị trí: tương đối; phông chữ:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; màu sắc:#fff; lề-top:8px;","buttoncss">display:block; vị trí: tương đối; Margin-top:8px;","texteffectsensitive":true,"texteffectResponsesize":640,"titlecss responsive":"font-size:12px;","descriptioncss responsive":"display:none !important;","buttoncss responsive": "","addgooglefonts":false,"googlefonts":"textleftrightpercentforstatic":40))