INDEX MATCH trong Google Sheets – một cách khác để tra cứu dọc

Khi bạn cần tìm dữ liệu trong trang tính tương ứng với một bản ghi khóa nhất định, đó thường là Google Sheets VLOOKUP mà bạn chuyển sang. Nhưng bạn đã đi: VLOOKUP tát bạn với những hạn chế gần như ngay lập tức. Đó là lý do tại sao bạn nên tăng tài nguyên cho nhiệm vụ bằng cách học INDEX MATCH.

INDEX MATCH trong Google Sheets là sự kết hợp của hai chức năng: INDEX và MATCH. Khi được sử dụng song song, chúng hoạt động như một sự thay thế tốt hơn cho Google Sheets VLOOKUP. Hãy cùng tìm hiểu khả năng của họ trong bài viết trên blog này. Nhưng trước tiên, tôi muốn cung cấp cho bạn một chuyến tham quan nhanh về vai trò của chính họ trong bảng tính.

Chức năng MATCH của Google Sheets

Tôi muốn bắt đầu với Google Sheets MATCH vì nó thực sự đơn giản. Nó quét dữ liệu của bạn cho một giá trị cụ thể và trả về vị trí của nó:

= MATCH (search_key, phạm vi, [search_type])

Đây là một ví dụ: để có được vị trí của một loại quả mọng nhất định trong danh sách tất cả các loại quả mọng, tôi cần công thức MATCH sau trong Google Sheets của mình:

=MATCH("Blueberry",A1:A10,0)

Tra cứu kết quả khớp chính xác bằng chức năng Google Sheets MATCH.

Chức năng INDEX của Google Sheets

Trong khi MATCH hiển thị vị trí cần tìm giá trị của bạn (vị trí của nó trong phạm vi), hàm INDEX của Google Sheets sẽ tự tìm nạp giá trị dựa trên giá trị hàng và cột của nó:

= INDEX (tham khảo,

, [column])

  • tài liệu tham khảo là phạm vi để tìm trong. Bắt buộc.
  • hàng là số lượng hàng để bù từ ô đầu tiên trong phạm vi của bạn. Tùy chọn, 0 nếu bỏ qua.
  • cột, giống như hàng, là số lượng cột bù. Cũng tùy chọn, cũng 0 nếu bỏ qua.

Nếu bạn chỉ định cả hai đối số tùy chọn (hàng và cột), Google Sheets INDEX sẽ trả về một bản ghi từ một ô đích:

=INDEX(A1:C10,7,1)

Sử dụng INDEX cho Google Sheets để lấy bản ghi từ một ô cụ thể.
Bỏ qua một trong những đối số đó và hàm sẽ giúp bạn có toàn bộ hàng hoặc cột tương ứng:

=INDEX(A1:C10,7)

Bỏ qua đối số cột để tìm nạp toàn bộ hàng.

Cách sử dụng INDEX MATCH trong Google Sheets – ví dụ về công thức

Khi INDEX và MATCH được sử dụng cùng nhau trong bảng tính, chúng ở mức mạnh nhất. Họ hoàn toàn có thể thay thế Google Sheets VLOOKUP và tìm nạp bản ghi cần thiết từ một bảng dựa trên giá trị khóa của bạn.

Xây dựng công thức INDEX MATCH đầu tiên của bạn cho Google Sheets

Giả sử bạn muốn lấy thông tin chứng khoán trên cranberry từ cùng một bảng tôi đã sử dụng ở trên. Tôi chỉ trao đổi cột B và C (bạn sẽ tìm hiểu lý do tại sao một lát sau).

  1. Bây giờ tất cả các loại quả mọng được liệt kê trong cột C. Chức năng MATCH của Google Sheets sẽ giúp bạn xác định hàng chính xác của quả nam việt quất: số 8=MATCH("Cranberry",C1:C10,0)
  2. Đặt toàn bộ công thức MATCH thành một hàng đối số trong hàm INDEX:=INDEX(A1:C10,MATCH("Cranberry",C1:C10,0))

    Điều này sẽ trả lại toàn bộ hàng với cranberry trong đó.

  3. Nhưng vì tất cả những gì bạn cần là thông tin chứng khoán, hãy xác định số lượng cột tra cứu: 3=INDEX(A1:C10,MATCH("Cranberry",C1:C10,0),2)
  4. Voila!
    Sử dụng Google Sheets INDEX MATCH để tra cứu các giá trị.
  5. Bạn có thể đi xa hơn và từ bỏ chỉ báo cột cuối cùng đó (2). Bạn hoàn toàn không cần nó nếu bạn chỉ sử dụng cột tra cứu (B1: B10) chứ không phải toàn bộ bảng (A1: C10) là đối số đầu tiên:=INDEX(B1:B10,MATCH("Cranberry",C1:C10,0))

    Sử dụng một cột thay vì toàn bộ bảng làm đối số đầu tiên.

    Tiền boa. Một cách thuận tiện hơn để kiểm tra sự sẵn có của các loại quả mọng khác nhau là đặt chúng vào danh sách thả xuống (E 2) và giới thiệu hàm MATCH của bạn đến ô có danh sách đó:

    =INDEX(B1:B10,MATCH(E2,C1:C10,0))

    Khi bạn chọn quả mọng, giá trị liên quan sẽ thay đổi tương ứng:
    Tham khảo ô với danh sách thả xuống.

Tại sao INDEX MATCH trong Google Sheets tốt hơn VLOOKUP

Bạn đã biết rằng Google Sheets INDEX MATCH tìm giá trị của bạn trong một bảng và trả về một bản ghi liên quan khác từ cùng một hàng. Và bạn biết rằng Google Sheets VLOOKUP thực hiện chính xác như vậy. Vậy tại sao phải bận tâm?

Vấn đề là CHỈ SỐ INDEXmột số ưu điểm chính trên VLOOKUP:

  1. Có thể tra cứu bên trái. Tôi đã thay đổi các vị trí cột trước đó để minh họa điều này: Hàm INDEX MATCH trong Google Sheets có thể và nhìn sang bên trái của cột tìm kiếm. VLOOKUP luôn tìm kiếm cột đầu tiên của phạm vi và tìm kiếm các kết quả khớp bên phải – nếu không, nó chỉ bị lỗi # N / A:
    Nhìn lên bên trái với Google Sheets INDEX MATCH.
  2. Không làm rối các tham chiếu khi thêm các cột mới và di chuyển các cột hiện có. Nếu bạn thêm hoặc di chuyển các cột, INDEX MATCH sẽ tự động phản ánh các thay đổi mà không can thiệp vào kết quả. Vì bạn sử dụng tham chiếu cột, chúng được điều chỉnh ngay lập tức bởi Google Sheets:
    Xem cách các công thức điều chỉnh mà không làm hỏng kết quả.
    Hãy tiếp tục và cố gắng thực hiện điều này với VLOOKUP: nó yêu cầu số thứ tự thay vì tham chiếu ô cho cột tra cứu. Do đó, cuối cùng bạn sẽ nhận được giá trị sai vì một cột khác có cùng vị trí – cột 2 trong ví dụ của tôi:
    Vlookup kéo dữ liệu sai.
  3. Trường hợp văn bản người tiêu dùng khi cần thiết (thêm về điều này ngay bên dưới).
  4. Có thể được sử dụng cho tra cứu dọc dựa trên nhiều tiêu chí.

Tôi mời bạn nhìn vào hai điểm cuối cùng một cách chi tiết dưới đây.

Tra cứu v-case nhạy cảm với INDEX MATCH trong Google Sheets

INDEX MATCH là một hướng đi khi nói đến phân biệt chữ hoa chữ thường.

Giả sử tất cả các loại quả mọng đang được bán theo hai cách – lỏng lẻo (cân tại quầy) và đóng gói trong hộp. Do đó, có hai lần xuất hiện của mỗi loại quả mọng được viết trong các trường hợp khác nhau trong danh sách, mỗi loại có ID riêng cũng khác nhau trong các trường hợp:
Cùng tên, cùng ID, trường hợp văn bản khác nhau.

Vì vậy, làm thế nào bạn có thể tra cứu thông tin chứng khoán trên một quả mọng được bán theo một cách nhất định? VLOOKUP sẽ trả lại tên đầu tiên mà nó tìm thấy bất kể trường hợp của nó.

May mắn thay, INDEX MATCH cho Google Sheets có thể làm điều đó một cách chính xác. Bạn sẽ chỉ cần sử dụng một chức năng bổ sung – TÌM hoặc CHÍNH XÁC.

Ví dụ 1. TÌM cho Vlookup phân biệt chữ hoa chữ thường

FIND là một chức năng phân biệt chữ hoa chữ thường trong Google Sheets, làm cho nó tuyệt vời cho tra cứu dọc phân biệt chữ hoa chữ thường:

=ArrayFormula(INDEX(B2:B19,MATCH(1,FIND(E2,C2:C19)),0))

Sử dụng hàm FIND để xây dựng INDEX MATCH phân biệt chữ hoa chữ thường.
Hãy xem điều gì xảy ra trong công thức này:

  1. TÌM quét cột C (C2: C19) cho hồ sơ từ E 2 (quả anh đào) xem xét trường hợp thư của nó. Khi được định vị, công thức “đánh dấu” ô đó bằng một số – 1.
  2. MATCH tìm kiếm cho nhãn hiệu này – 1 – trong cùng một cột (C) và trao số hàng của nó cho INDEX.
  3. INDEX đi xuống hàng đó trong cột B (B2: B19) và lấy bản ghi yêu cầu cho bạn.
  4. Khi bạn hoàn thành việc xây dựng công thức, nhấn Ctrl + Shift + Enter để thêm ArrayFormula ở đầu. Điều này là bắt buộc vì không có nó TÌM sẽ không thể tìm kiếm trong các mảng (trong nhiều hơn một ô). Hoặc bạn có thể gõ ‘ArrayFormula‘từ bàn phím của bạn.

Ví dụ 2. CHÍNH XÁC cho Vlookup phân biệt chữ hoa chữ thường

Nếu bạn thay thế TÌM bằng CHÍNH XÁC, cái sau sẽ tìm các bản ghi với cùng các ký tự, bao gồm cả trường hợp văn bản của chúng.

Sự khác biệt duy nhất là CHÍNH XÁC “đánh dấu” một trận đấu với THẬT thay vì số 1. Do đó, đối số đầu tiên cho MATCH phải là THẬT:

=ArrayFormula(INDEX(B2:B19,MATCH(TRUE,EXACT(E2,C2:C19),0)))

Sử dụng hàm EXACT để xây dựng INDEX MATCH phân biệt chữ hoa chữ thường.

Google Sheets INDEX MATCH với nhiều tiêu chí

Điều gì xảy ra nếu có một số điều kiện dựa trên đó bạn muốn lấy bản ghi?

Hãy kiểm tra giá của quả anh đào đang được bán ở Xô PP và đã gần hết:
Tìm hiểu giá trên một số tiêu chí.
Tôi đã sắp xếp tất cả các tiêu chí trong danh sách thả xuống trong cột F. Và đó là Google Sheets INDEX MATCH hỗ trợ nhiều tiêu chí, không phải là VLOOKUP. Đây là công thức bạn sẽ cần sử dụng:

=ArrayFormula(INDEX(B2:B24,MATCH(CONCATENATE(F2:F4),A2:A24&C2:C24&D2:D24,0),))

Cách sử dụng INDEX MATCH trong Google Sheets với nhiều tiêu chí.
Đừng hoảng sợ! 🙂 Logic của nó thực sự khá đơn giản:

  1. CONCATENATE (F2: F4) kết hợp cả ba bản ghi từ các ô với các tiêu chí thành một chuỗi như thế này:CherryPP xô ra

    Đây là một tìm kiếm_key cho MATCH, hay nói cách khác, những gì bạn đang tìm kiếm trong bảng.

  2. A2: A24 & C2: C24 & D2: D24 tạo thành phạm vi để hàm MATCH tìm kiếm. Vì cả ba tiêu chí diễn ra trong ba cột riêng biệt, theo cách này, bạn kết hợp chúng với nhau:Khay CherryCard Trong kho
    CherryFilm bao bì
    CherryPP xô ra
    Vân vân.
  3. Đối số cuối cùng trong MATCH – 0 – làm cho nó có thể tìm thấy kết quả khớp chính xác cho CherryPP xô ra trong số tất cả các hàng cột kết hợp. Như bạn có thể thấy, nó ở hàng thứ 3.
  4. Và sau đó INDEX thực hiện công việc của mình: nó tìm nạp bản ghi từ hàng thứ 3 của cột B.
  5. ArrayFormula được sử dụng để cho phép các chức năng khác hoạt động với mảng.

Tiền boa. Nếu công thức của bạn không tìm thấy kết quả khớp, nó sẽ trả về lỗi. Để tránh điều đó, bạn có thể gói toàn bộ công thức này trong IFERROR (biến nó thành đối số đầu tiên) và nhập bất cứ điều gì bạn muốn thấy trong một ô thay vì lỗi như một đối số thứ hai:

=IFERROR(ArrayFormula(INDEX(B2:B27,MATCH(CONCATENATE(F2:F4),A2:A27&C2:C27&D2:D27,0),)),"Not found")

Sử dụng IFERROR để ghi đè các lỗi tiềm ẩn.

Thay thế tốt hơn cho INDEX MATCH trong Google Sheets – Nhiều trận đấu VLOOKUP

Bất cứ chức năng tra cứu nào bạn thích, VLOOKUP hoặc INDEX MATCH, đều có sự thay thế tốt hơn cho cả hai.

Nhiều trận đấu VLOOKUP là một tiện ích bổ sung đặc biệt dành cho Google Sheets được thiết kế để:

  • tra cứu mà không có công thức
  • tra cứu theo mọi hướng
  • tìm kiếm theo nhiều điều kiện cho các loại dữ liệu khác nhau: văn bản, số, ngày, thời gian, Vân vân.
  • tìm nạp một số trận đấu, bao nhiêu tùy ý bạn (tất nhiên là có nhiều như vậy trong bảng của bạn)

Giao diện rất đơn giản, vì vậy bạn sẽ không phải nghi ngờ liệu bạn có đang làm mọi thứ chính xác hay không:

  1. Chọn phạm vi nguồn.
  2. Đặt số lượng trận đấu và cột để trả về.
  3. Tinh chỉnh các điều kiện bằng cách sử dụng các toán tử được xác định trước (chứa, =, không trống, giữa, Vân vân.).

Tinh chỉnh các điều kiện trong Nhiều trận đấu VLOOKUP.
Bạn cũng sẽ có thể:

  • xem trước kết quả
  • quyết định nơi đặt nó
  • và làm thế nào: như một công thức hoặc chỉ các giá trị

Xem trước kết quả trong Nhiều trận đấu VLOOKUP.
Đừng bỏ lỡ cơ hội này để kiểm tra tiện ích bổ sung. Đi trước và cài đặt nó từ G Suite Marketplace. Nó là trang hướng dẫn sẽ giải thích mọi lựa chọn chi tiết.

Hẹn gặp lại trong các bình luận dưới đây hoặc trong bài viết tiếp theo;)

Bạn cũng có thể quan tâm:

Source link

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *