VLOOKUP với MATCH | Tạo công thức linh hoạt với VLOOKUP MATCH

Công thức Vlookup chỉ hoạt động khi mảng bảng trong công thức không thay đổi, nhưng nếu có một cột mới được chèn vào bảng hoặc một cột bị xóa, công thức sẽ cho kết quả không chính xác hoặc phản ánh lỗi, để làm cho công thức không có lỗi trong những tình huống động như vậy, chúng tôi sử dụng hàm đối sánh để thực sự khớp với chỉ mục của dữ liệu và trả về kết quả thực tế.

Kết hợp hàm VLOOKUP với Match

Công thức vlookup là hàm được sử dụng phổ biến nhất được sử dụng để tìm kiếm và trả về cùng một giá trị trong chỉ mục cột được chỉ định hoặc giá trị từ một chỉ mục cột khác có tham chiếu đến giá trị khớp từ cột đầu tiên. Thách thức lớn phải đối mặt khi sử dụng vlookup là chỉ mục cột được chỉ định là tĩnh và không có chức năng động. Đặc biệt là khi bạn đang làm việc trên nhiều tiêu chí yêu cầu bạn phải thay đổi chỉ mục cột tham chiếu theo cách thủ công. Do đó, nhu cầu này được đáp ứng bằng cách sử dụng công thức "MATCH" để nắm bắt hoặc kiểm soát tốt hơn chỉ mục cột thường xuyên thay đổi trong công thức VLOOKUP.

VLookup và công thức đối sánh

# 1 - Công thức VLOOKUP

Công thức của hàm VLOOKUP trong Excel

Ở đây, tất cả các đối số được nhập là bắt buộc.

  • Lookup_value - Tại đây ô tham chiếu hoặc văn bản có dấu ngoặc kép phải được nhập để được xác định trong phạm vi cột.
  • Mảng bảng -   Đối số này yêu cầu nhập phạm vi bảng nơi Lookup_value sẽ được tìm kiếm và dữ liệu được truy xuất nằm trong phạm vi cột cụ thể.
  • Col_index_num - Trong đối số này, số chỉ mục cột hoặc số lượng cột từ cột đầu tiên tham chiếu cần được nhập từ đó giá trị tương ứng cần được kéo từ cùng một vị trí với giá trị được tìm kiếm trong cột đầu tiên.
  • [Range_lookup] - Đối số này sẽ đưa ra hai tùy chọn.
  • TRUE - Đối sánh gần đúng: - Đối số có thể được nhập dưới dạng TRUE hoặc số “1”, trả về đối sánh gần đúng tương ứng với cột tham chiếu hoặc cột đầu tiên. Hơn nữa, các giá trị trong cột đầu tiên của mảng bảng phải được sắp xếp theo thứ tự tăng dần.
  • FALSE - Đối sánh chính xác: - Ở đây đối số được nhập có thể là FALSE hoặc số “0”. Tùy chọn này sẽ chỉ trả về kết quả khớp chính xác của giá trị tương ứng được xác định từ vị trí trong phạm vi cột đầu tiên. Việc không tìm kiếm giá trị từ cột đầu tiên sẽ trả về thông báo lỗi “# N / A”.

# 2 - Công thức đối sánh

Hàm đối sánh trả về vị trí ô của giá trị được nhập cho mảng bảng đã cho.

Tất cả các đối số trong cú pháp là bắt buộc.

  • Lookup_value - Ở đây đối số được nhập có thể là tham chiếu ô của giá trị hoặc một chuỗi văn bản có dấu ngoặc kép có vị trí ô được yêu cầu để được kéo.
  • Lookup_array - Dải mảng cho bảng được yêu cầu nhập có giá trị hoặc nội dung ô muốn được xác định.
  • [loại đối sánh] - Đối số này cung cấp ba tùy chọn như được giải thích bên dưới.
  • “1-Nhỏ hơn” - Ở đây đối số được nhập là số “1” sẽ trả về giá trị nhỏ hơn hoặc bằng giá trị tra cứu. Và mảng tra cứu cũng phải được sắp xếp theo thứ tự tăng dần.
  • “0-Đối sánh chính xác” - Ở đây đối số được nhập phải là số “0”. Tùy chọn này sẽ trả về vị trí chính xác của giá trị tra cứu phù hợp. Tuy nhiên, mảng tra cứu có thể theo bất kỳ thứ tự nào.
  • “-1-Lớn hơn” -  Đối số được nhập phải là số “-1”. Tùy chọn thứ ba tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tra cứu. Ở đây thứ tự cho mảng tra cứu phải được đặt theo thứ tự giảm dần.

# 3 - VLOOKUP với Công thức MATCH

= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])

Làm thế nào để sử dụng hàm VLOOKUP với công thức đối sánh trong Excel?

Ví dụ dưới đây sẽ giúp hiểu chức năng của vlookup và công thức đối sánh khi kết hợp với nhau.

Bạn có thể tải xuống VLookup này với Mẫu Match Excel tại đây - VLookup với Mẫu Match Excel

Hãy xem xét bảng dữ liệu dưới đây mô tả các thông số kỹ thuật của chiếc xe nhất định sẽ được mua.

Để có được sự rõ ràng của hàm kết hợp cho vlookup và hàm đối sánh, chúng ta hãy hiểu cách hoạt động của từng công thức riêng lẻ và sau đó đi đến kết quả đối sánh vlookup khi được đặt cùng nhau.

Bước # 1 - Hãy để chúng tôi áp dụng công thức vlookup ở cấp độ cá nhân để đi đến kết quả.

Đầu ra được hiển thị bên dưới:

Ở đây giá trị tra cứu được tham chiếu đến $ B9 là mô hình “E” và mảng tra cứu được cho là phạm vi của bảng dữ liệu với giá trị tuyệt đối là “$”, chỉ số cột được tham chiếu đến cột “4” là số cột "Loại" và tra cứu phạm vi được cung cấp một kết quả khớp chính xác.

Do đó, công thức sau được áp dụng để trả về giá trị cho cột "Nhiên liệu".

Đầu ra được hiển thị bên dưới:

Ở đây, giá trị tra cứu với chuỗi tuyệt đối “$” được áp dụng cho giá trị tra cứu và lookup_array giúp sửa ô tham chiếu ngay cả khi công thức đang được sao chép sang một ô khác. Trong cột "Nhiên liệu", chúng ta cần thay đổi chỉ mục cột thành "5" vì giá trị mà dữ liệu cần được truy xuất thay đổi.

Bước # 2 -  Bây giờ chúng ta hãy áp dụng công thức Khớp để truy xuất vị trí cho giá trị tra cứu đã cho.

Đầu ra được hiển thị bên dưới:

Như có thể thấy trong ảnh chụp màn hình ở trên, ở đây chúng tôi đang cố gắng lấy lại vị trí cột từ mảng bảng. Trong trường hợp này, số cột được kéo được gọi là ô C8 là cột "Loại" và phạm vi tra cứu được tìm kiếm được cung cấp dưới dạng phạm vi tiêu đề cột và loại đối sánh được cung cấp một đối sánh chính xác là " 0 ”.

Do đó, bảng dưới đây sẽ cho kết quả mong muốn cho các vị trí của cột “Nhiên liệu”.

Bây giờ ở đây cột cần tìm kiếm được cho là ô D8 và chỉ mục cột mong muốn được trả về là “5”.

Bước # 3 - Bây giờ công thức Khớp sẽ được sử dụng trong hàm vlookup để lấy giá trị từ vị trí cột đã xác định.

Đầu ra được hiển thị bên dưới:

Trong công thức trên, hàm đối sánh được đặt thay cho tham số chỉ số cột của hàm vlookup. Tại đây, hàm đối sánh sẽ xác định ô tham chiếu giá trị tra cứu “C8” và trả về số cột thông qua mảng bảng đã cho. Vị trí cột này sẽ phục vụ mục đích như một đầu vào cho đối số chỉ mục cột trong hàm vlookup. Điều nào sẽ giúp vlookup xác định giá trị được trả về từ số chỉ mục cột kết quả?

Tương tự, chúng tôi cũng đã áp dụng vlookup với công thức đối sánh cho cột "Nhiên liệu".

Đầu ra được hiển thị bên dưới:

Do đó, chúng tôi có thể áp dụng hàm kết hợp này cho các cột khác "Loại" và "Nhiên liệu".

Những điều cần ghi nhớ

  • VLOOKUP chỉ có thể được áp dụng cho các giá trị tra cứu ở phía ngoài cùng bên trái của nó. Bất kỳ giá trị nào hiện có được tìm kiếm ở phía bên phải của bảng dữ liệu sẽ trả về giá trị lỗi “# N / A”.
  • Phạm vi của table_array được nhập trong đối số thứ hai phải là tham chiếu ô tuyệt đối “$”, điều này sẽ duy trì phạm vi mảng bảng cố định khi áp dụng công thức tra cứu cho các ô khác, nếu không các ô tham chiếu cho phạm vi mảng bảng sẽ chuyển sang ô tiếp theo tài liệu tham khảo.
  • Giá trị được nhập trong giá trị tra cứu không được nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của mảng bảng, nếu không hàm sẽ trả về giá trị lỗi “# N / A”.
  • Trước khi áp dụng đối sánh gần đúng “TRUE” hoặc “1” trong đối số cuối cùng, hãy luôn nhớ sắp xếp mảng bảng theo thứ tự tăng dần.
  • Hàm đối sánh chỉ trả về vị trí của giá trị trong mảng bảng vlookup và không trả về giá trị.
  • Trong trường hợp Hàm đối sánh không thể xác định vị trí của giá trị tra cứu trong mảng bảng thì công thức trả về “# N / A” trong giá trị lỗi.
  • Các hàm vlookup và so khớp không phân biệt chữ hoa chữ thường khi so khớp giá trị tra cứu với giá trị văn bản phù hợp trong mảng bảng.

$config[zx-auto] not found$config[zx-overlay] not found