Khớp chỉ mục không hoạt động trên các trang tính

INDEX và MATCH là hai hàm thường được sử dụng cùng nhau nhất. Chúng vượt trội hơn nhiều so với chức năng VLOOKUP thậm chí có thể được sử dụng nhiều hơn

Trong ví dụ dưới đây, chúng tôi sẽ trình bày cách sử dụng INDEX và MATCH trong nhiều trang tính

tóm tắt nhanh

Hàm INDEX trong Excel dùng để trả về giá trị của một ô tại một vị trí nhất định trong một dải ô hoặc mảng

Cú pháp của hàm này như sau

1

=INDEX(mảng, row_num, [col_num], [area_num])

Đối số là

mảng – Nó có thể là một phạm vi ô, bảng, văn bản hoặc bất kỳ thứ gì mà giá trị của chúng ta được tìm thấy

row_num – Điều này đại diện cho một vị trí hàng trong mảng đã xác định

col_num – [tùy chọn] Điều này đại diện cho một tùy chọn cột trong mảng đã xác định

area_num – [tùy chọn] Ở đây chúng tôi nhập phạm vi tham chiếu mà chúng tôi nên sử dụng

Hàm MATCH được sử dụng để tìm kiếm một mục đã chỉ định trong một dải ô và sau đó trả về vị trí tương đối của mục đó trong dải ô

Cú pháp của nó là

1

=MATCH(lookup_value, lookup_array, [match_type])

Lập luận như sau

lookup_value. Đây là một lĩnh vực cần thiết. Ở đây chúng tôi nhập giá trị mà chúng tôi muốn tìm trong lookup_array

tra cứu_array. Cũng cần thiết. Chúng tôi khai báo phạm vi nơi giá trị của chúng tôi được đặt

Loại so khớp. Đây là trường tùy chọn có thể có ba giá trị. -1, 0 hoặc 1. Đối số này xác định cách Excel so khớp lookup_value với các giá trị trong lookup_array. Giá trị 0 đại diện cho giá trị chính xác của lookup_value và chúng tôi sẽ sử dụng giá trị này

Lập chỉ mục và khớp từ một trang tính khác

Ví dụ của chúng tôi, chúng tôi sẽ sử dụng danh sách tất cả các phim Marvel, cùng với xếp hạng doanh thu đáng nể của chúng (so với các phim Marvel khác trong danh sách), năm phát hành, ngày phát hành ở Hoa Kỳ và tổng doanh thu phòng vé. Chúng ta sẽ đặt bảng này thành một sheet có tên là “Phim Marvel”

Để tạo bảng Chỉ mục và Đối sánh, trước tiên chúng ta sẽ tạo một trang tính khác và gọi nó đơn giản là „Chỉ mục & Đối sánh“

Trong trang tính đó, các giá trị trong cột A sẽ như sau (đây là những giá trị có thể tìm thấy ở hàng đầu tiên của bảng gốc của chúng tôi)

Xếp hạng doanh thu sẽ là giá trị tra cứu của chúng tôi cho row_num (giá trị này sẽ nằm trong ô B1), trong khi các giá trị khác trong cột A sẽ là giá trị tra cứu của chúng tôi cho column_num của hàm INDEX

Ghi nhớ điều đó, chúng tôi sẽ đặt ngẫu nhiên số 3 vào ô B1

Tại ô B2 chúng ta sẽ nhập công thức như sau

1

=INDEX('Marvel Movies'. A. E,MATCH($B$1,'Marvel Movies'. B. B,0),MATCH($A2,'Marvel Movies'. $1. $1,0))

Chúng tôi sẽ giải thích chi tiết công thức dưới đây

Đầu tiên, chúng tôi bắt đầu bằng cách nhập công thức INDEX của chúng tôi

Đối số đầu tiên mà chúng ta phải nhập vào công thức là một mảng. Vì chúng tôi sẽ cần dữ liệu từ toàn bộ bảng, nên mảng của chúng tôi sẽ là mọi ô trong phạm vi từ cột A đến cột E

Vì vậy, mảng của chúng tôi là 'Phim Marvel'. A. E (Trong phần đầu tiên của chuỗi, chúng tôi đang tham chiếu trang tính có dữ liệu. Đó là lý do tại sao chúng tôi có dấu nháy đơn xung quanh tên của trang tính của chúng tôi. Chúng ta có ". ” cũng vì đó cũng là một phần bắt buộc khi tham khảo trang tính khác. Cuối cùng, chúng tôi đã thêm các cột)

Tiếp theo, chúng ta cần xác định row_num, i. e. chúng ta phải tìm một vị trí của ô mong muốn trong các hàng. Đó là nơi hàm MATCH xuất hiện. Chúng tôi nhập nó và chúng tôi nhập giá trị tra cứu mong muốn (ô B1). Chúng tôi khóa ô này vì chúng tôi muốn sử dụng tham chiếu của ô này để tìm kiếm các giá trị khác nhau

Sau đó, chúng tôi xác định lookup_array (nơi có thể đặt giá trị này trong bảng của chúng tôi. Chúng tôi biết đó là cột B, vì vậy chúng tôi tham chiếu toàn bộ cột trong trang tính “Phim Marvel” và match_type (0 để tìm kiếm giá trị chính xác)

Cuối cùng, chúng ta phải xác định column_row. Ô tham chiếu là ô A2. Chúng tôi sẽ chỉ khóa cột cho ô này, vì vậy nó sẽ luôn là cột A, nhưng các hàng có thể được thay đổi. Lookup_array của chúng tôi lại được tìm thấy trong trang tính “Phim Marvel” và giá trị nằm ở hàng đầu tiên, do đó có cú pháp. ‘Phim Marvel’. $1. $1

Chúng ta cũng cần khóa các ô này, vì tất cả các giá trị của chúng ta nằm trong cột A trong trang tính này đều nằm ở hàng đầu tiên trong trang tính “Phim Marvel”. match_type của chúng ta lại là 0

Kết quả của công thức của chúng tôi là như sau

Với công thức này và các ô được xác định theo cách này, cũng như các ô bị khóa và thứ tự của các cột trong cột đầu tiên, chúng ta chỉ cần sao chép và dán công thức của mình vào các ô trong cột B (từ B2 đến B5)

Khi chúng tôi làm điều đó, chúng tôi sẽ có tất cả dữ liệu của mình

Công thức tại ô B3 như sau

1

=INDEX('Marvel Movies'. A. E,MATCH($B$1,'Marvel Movies'. B. B,0),MATCH($A3,'Marvel Movies'. $1. $1,0))

Chúng ta có thể thấy rằng, so với công thức đầu tiên mà chúng ta đã giải thích, sự khác biệt duy nhất là bây giờ chúng ta có ô A3 thay vì ô A2 làm giá trị tra cứu trong công thức MATCH thứ hai, xác định cột_num cho công thức INDEX của chúng ta

Chúng tôi có thể chỉ sử dụng dữ liệu từ bảng. Chúng tôi sẽ làm điều đó cho năm phát hành. Công thức trông như thế này

1

=INDEX(Bảng1[#All],MATCH($B$1,Table1[[#All],[Revenue rating]],0),MATCH($A5,Table1[#Headers],0))

Như đã thấy, đối với mảng INDEX Table1[#All] được sử dụng. Table1 là tên của bảng của chúng tôi và „All“ là viết tắt của tất cả dữ liệu

Lookup_array cho hàm MATCH đầu tiên là Bảng1[[#Tất cả],[Xếp hạng doanh thu]], i. e cột chứa xếp hạng doanh thu

Lookup_array cho hàm MATCH thứ hai là Tiêu đề từ bảng của chúng tôi, là hàng đầu tiên

Nếu chúng ta thêm một hàng hoặc một cột vào bảng của mình, hàng hoặc cột này sẽ trở thành một phần không thể thiếu của bảng và chúng ta có thể áp dụng một công thức hiện có để tìm giá trị mong muốn

Tại sao đối sánh INDEX của tôi không hoạt động?

Khớp INDEX có hoạt động trong Google Trang tính không?

Bạn có thể sử dụng khớp INDEX theo chiều ngang không?

Bạn có thể khớp INDEX trên nhiều cột không?