INDEX MATCH MATCH và các cách khác để thực hiện tra cứu hai chiều trong Excel

Nội dung chính

[ad_1]

Hướng dẫn giới thiệu một vài công thức khác nhau để thực hiện tra cứu hai chiều trong Excel. Chỉ cần xem qua các lựa chọn thay thế và chọn yêu thích của bạn 🙂

Khi tìm kiếm thứ gì đó trong bảng tính Excel của bạn, hầu hết thời gian bạn sẽ tìm kiếm theo chiều dọc trong các cột hoặc theo chiều ngang trong các hàng. Nhưng đôi khi bạn cần nhìn qua cả hàng và cột. Nói cách khác, bạn đặt mục tiêu tìm giá trị tại giao điểm của một hàng và cột nhất định. Cái này được gọi là tra cứu ma trận (còn gọi là 2 chiều hoặc là Tra cứu 2 chiều) và hướng dẫn này cho thấy cách thực hiện theo 4 cách khác nhau.

Công thức MATCH MATCH MATCH

Cách phổ biến nhất để thực hiện tra cứu hai chiều trong Excel là sử dụng INDEX MATCH MATCH. Đây là một biến thể của cổ điển Công thức INDEX MATCH mà bạn thêm một hàm MATCH nữa để có được cả số hàng và số cột:

MỤC LỤC (data_array, TRẬN ĐẤU (vlookup_value, lookup_column_range, 0), MATCH (giá trị hlookup, tra cứu_row_range, 0))

Ví dụ, hãy tạo một công thức để kéo một quần thể động vật nào đó trong một năm nhất định từ bảng dưới đây. Để bắt đầu, chúng tôi xác định tất cả các đối số:

Dữ liệu_array – B2: E4 (ô dữ liệu, không bao gồm các tiêu đề hàng và cột)
Vlookup_value – H1 (động vật mục tiêu)
Tra cứu_column_range – A2: A4 (tiêu đề hàng: tên động vật) – A3: A4
Hlookup_value – H2 (năm mục tiêu)
Tra cứu_row_range – B1: E1 (tiêu đề cột: năm)

Đặt tất cả các đối số lại với nhau và bạn sẽ nhận được công thức này để tra cứu hai chiều:

=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))

INDEX MATCH MATCH công thức để tra cứu trong các hàng và cột

Công thức này hoạt động như thế nào

Mặc dù thoạt nhìn có vẻ hơi phức tạp, logic của công thức thực sự đơn giản và dễ hiểu. Các MỤC LỤC Hàm lấy một giá trị từ mảng dữ liệu dựa trên số hàng và số cột và hai TRẬN ĐẤU các hàm cung cấp các số đó:

INDEX(B2:E4, row_num, column_num)

Ở đây, chúng tôi tận dụng khả năng của MATCH (lookup_value, lookup_array, [match_type]) để trả lại một vị trí tương đối của tra cứu_ giá trị trong tra cứu_array.

Vì vậy, để có được số hàng, chúng tôi tìm kiếm con vật quan tâm (H1) trên các tiêu đề hàng (A2: A4):

MATCH(H1, A2:A4, 0)

Để lấy số cột, chúng tôi tìm kiếm năm mục tiêu (H2) trên các tiêu đề cột (B1: E1):

MATCH(H2, B1:E1, 0)

Trong cả hai trường hợp, chúng tôi tìm kiếm sự trùng khớp chính xác bằng cách đặt đối số thứ 3 thành 0.

Trong ví dụ này, MATCH đầu tiên trả về 2 vì giá trị vlookup của chúng tôi (gấu Bắc cực) được tìm thấy trong A3, đó là 2^thứ ô trong A2: A4. MATCH thứ hai trả về 3 vì giá trị hlookup (2000) được tìm thấy trong D1, là 3^{lần thứ} ô trong B1: E1.

Cho ở trên, công thức giảm xuống:

INDEX(B2:E4, 2, 3)

Và trả về một giá trị tại giao điểm của 2^thứ hàng và 3^{lần thứ} cột trong mảng dữ liệu B2: E4, là một giá trị trong ô D3.

Công thức VLOOKUP và MATCH để tra cứu 2 chiều

Một cách khác để thực hiện tra cứu hai chiều trong Excel là sử dụng kết hợp các hàm VLOOKUP và MATCH:

VLOOKUP (vlookup_value, bảng_array, TRẬN ĐẤU(hlookup_value, tra cứu_row_range, 0), SAI)

Đối với bảng mẫu của chúng tôi, công thức có hình dạng sau:

=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)

Ở đâu:

Bảng_array – A2: E4 (ô dữ liệu bao gồm các tiêu đề hàng)
Vlookup_value – H1 (động vật mục tiêu)
Hlookup_value – H2 (năm mục tiêu)
Tra cứu_row_range – A1: E1 (tiêu đề cột: năm)

Tra cứu hai chiều bằng cách sử dụng VLOOKUP và MATCH

Công thức này hoạt động như thế nào

Cốt lõi của công thức là Chức năng VLOOKUP được định cấu hình cho khớp chính xác (đối số cuối được đặt thành FALSE), tìm kiếm giá trị tra cứu (H1) trong cột đầu tiên của mảng bảng (A2: E4) và trả về giá trị từ một cột khác trong cùng hàng. Để xác định cột nào trả về giá trị từ đó, bạn sử dụng hàm MATCH cũng được cấu hình cho khớp chính xác (đối số cuối được đặt thành 0):

MATCH(H2, A1:E1, 0)

MATCH tìm kiếm giá trị trong H2 trên các tiêu đề cột (A1: E1) và trả về vị trí tương đối của ô tìm thấy. Trong trường hợp của chúng tôi, năm mục tiêu (2010) được tìm thấy trong E1, là 5^{thứ tự} trong mảng tra cứu. Vì vậy, số 5 đi đến col_index_num đối số của VLOOKUP:

VLOOKUP(H1, A2:E4, 5, FALSE)

VLOOKUP lấy nó từ đó, tìm một kết quả khớp chính xác cho giá trị tra cứu của nó trong A2 và trả về một giá trị từ 5^{thứ tự} cột trong cùng một hàng, đó là ô E2.

Lưu ý quan trọng! Để công thức hoạt động chính xác, bảng_array (A2: E4) của VLOOKUP và tra cứu_array của MATCH (A1: E1) phải có cùng số cột, nếu không thì số được MATCH chuyển đến col_index_num sẽ không chính xác (sẽ không tương ứng với vị trí của cột trong bảng_array).

Hàm XLOOKUP để tìm trong các hàng và cột

Gần đây, Microsoft đã giới thiệu thêm một chức năng trong Excel có nghĩa là thay thế tất cả các chức năng tra cứu hiện có như VLOOKUP, HLOOKUP và INDEX MATCH. Trong số những thứ khác, XLOOKUP có thể nhìn vào giao điểm của một hàng và cột cụ thể:

XLOOKUP (vlookup_value, vlookup_column_range, XLOOKUP (hlookup_value, hlookup_row_range, data_array))

Đối với tập dữ liệu mẫu của chúng tôi, công thức sẽ như sau:

=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))

INDEX MATCH MATCH công thức để tra cứu trong các hàng và cột

Ghi chú. Hiện tại XLOOKUP là một chức năng beta, chỉ dành cho những người đăng ký Office 365 là một phần của chương trình Người dùng nội bộ Office.

Công thức này hoạt động như thế nào

Công thức sử dụng khả năng của XLOOKUP để trả về toàn bộ một hàng hoặc cột. Hàm bên trong tìm kiếm năm mục tiêu trong hàng tiêu đề và trả về tất cả các giá trị cho năm đó (trong ví dụ này, cho năm 1980). Những giá trị đó đi đến return_array đối số của XLOOKUP bên ngoài:

XLOOKUP(H1, A2:A4, {22000;25000;700}))

Hàm XLOOKUP bên ngoài tìm kiếm động vật đích trên các tiêu đề cột và trả về giá trị ở cùng vị trí từ return_array.

Công thức SUMPRODVEL để tra cứu hai chiều

Các TÓM TẮT Hàm giống như một con dao Thụy Sĩ trong Excel – nó có thể làm rất nhiều việc ngoài mục đích được chỉ định, đặc biệt là khi đánh giá nhiều tiêu chí.

Để tra cứu hai tiêu chí, trong các hàng và cột, hãy sử dụng công thức chung này:

TÓM TẮT (vlookup_column_range = = vlookup_value) * (hlookup_row_range = = hlookup_value), data_array)

Để thực hiện tra cứu 2 chiều trong tập dữ liệu của chúng tôi, công thức như sau:

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)

Cú pháp dưới đây cũng sẽ hoạt động:

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)

Công thức SUMPRODVEL để tra cứu hai chiều trong Excel

Công thức này hoạt động như thế nào

Trọng tâm của công thức, chúng tôi so sánh hai giá trị tra cứu với các tiêu đề hàng và cột (động vật mục tiêu trong H1 so với tất cả các tên động vật trong A2: A4 và năm mục tiêu trong H2 so với tất cả các năm trong B1: E1):

(A2:A4=H1) * (B1:E1=H2)

Điều này dẫn đến 2 mảng giá trị TRUE và FALSE, trong đó đại diện của TRUE khớp với:

{FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}

Hoạt động nhân đã ép các giá trị TRUE và FALSE thành 1 và 0 và tạo ra một mảng hai chiều gồm 4 cột và 3 hàng (các hàng được phân tách bằng dấu chấm phẩy và mỗi cột dữ liệu bằng dấu phẩy):

{0,0,0,0;0,0,0,0;0,1,0,0}

Các hàm SUMPRODVEL nhân các phần tử của mảng trên với các mục của B2: E4 trong cùng một vị trí:

{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}

Và bởi vì nhân với số 0 cho số không, chỉ có mục tương ứng với 1 trong mảng đầu tiên tồn tại:

SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})

Cuối cùng, SUMPRODVEL cộng các phần tử của mảng kết quả và trả về giá trị 2000.

Ghi chú. Nếu bảng của bạn có nhiều hơn một hàng hoặc / và các tiêu đề cột có cùng tên, mảng cuối cùng sẽ chứa nhiều hơn một số khác 0 và tất cả các số đó sẽ được thêm vào. Kết quả là, bạn sẽ nhận được một tổng các giá trị đáp ứng cả hai tiêu chí. Đó là những gì làm cho công thức SUMPRODVEL khác với INDEX MATCH MATCH và VLOOKUP, trả về kết quả khớp đầu tiên được tìm thấy.

Tra cứu ma trận với các phạm vi được đặt tên

Một cách đơn giản hơn đáng kinh ngạc để thực hiện tra cứu ma trận trong Excel là sử dụng các phạm vi được đặt tên. Đây là cách thực hiện:

Phần 1: Tên cột và hàng

Cách nhanh nhất để đặt tên cho từng hàng và từng cột trong bảng của bạn là:

Chọn toàn bộ bảng (A1: E4 trong trường hợp của chúng tôi).
Trên Công thức trong tab Tên được xác định nhóm, nhấp Tạo từ lựa chọn hoặc nhấn Ctrl + Shift + F3 đường tắt.
bên trong Tạo tên từ lựa chọn hộp thoại, chọn Hàng đầu và Cột bên trái, và nhấn OK.

Điều này tự động tạo tên dựa trên các tiêu đề hàng và cột. Tuy nhiên, có một vài cảnh báo:

Nếu tiêu đề cột và / hoặc hàng của bạn là số hoặc chứa các ký tự cụ thể không được phép trong tên Excel, tên của các cột và hàng đó sẽ không được tạo. Để xem danh sách các tên đã tạo, hãy mở Trình quản lý tên (Ctrl + F3). Nếu một số tên bị thiếu, xác định chúng theo cách thủ công như được giải thích trong Cách đặt tên cho một phạm vi trong Excel.
Nếu một số tiêu đề hàng hoặc cột của bạn chứa khoảng trắng, ví dụ, khoảng trắng sẽ được thay thế bằng dấu gạch dưới Gấu Bắc cực.

Đối với bảng mẫu của chúng tôi, Excel sẽ tự động chỉ tạo các tên hàng. Tên cột phải được tạo thủ công vì các tiêu đề cột là số. Để khắc phục điều này, bạn chỉ cần mở đầu các số có dấu gạch dưới, như _1990.

Kết quả là, chúng ta có các phạm vi được đặt tên sau:
Phạm vi được đặt tên được tạo ra.

Phần 2: Tạo công thức tra cứu ma trận

Để kéo một giá trị tại giao điểm của một hàng và cột nhất định, chỉ cần nhập một trong các công thức chung sau vào một ô trống:

= =hàng tên tên cột dọc

Hoặc ngược lại:

= =tên cột dọc hàng tên= =

Ví dụ, để có được số lượng cá voi xanh vào năm 1990, công thức đơn giản như:

=Blue_whale _1990

Nếu ai đó cần hướng dẫn chi tiết hơn, các bước sau sẽ hướng dẫn bạn qua quy trình:

Trong một ô nơi bạn muốn kết quả xuất hiện, hãy nhập dấu bằng (=).
Bắt đầu nhập tên của hàng đích Cá voi xanh. Sau khi bạn nhập một vài ký tự, Excel sẽ hiển thị tất cả các tên hiện có khớp với đầu vào của bạn. Bấm đúp vào tên mong muốn để nhập nó trong công thức của bạn:
Sau tên hàng, nhập a không gian, hoạt động như toán tử giao nhau trong trường hợp này.
Nhập tên cột mục tiêu ( _1990 trong trường hợp của chúng ta).
Ngay sau khi nhập cả tên hàng và cột, Excel sẽ tô sáng hàng và cột tương ứng trong bảng của bạn và bạn nhấn Đi vào để hoàn thành công thức: