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)

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)

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)

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

   

   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:
   

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Ố INDEX có mộ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:
   
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:
   
   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:
   
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:

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

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

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

Đừ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.

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

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ị

Đừ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: