Hướng dẫn này sẽ dạy bạn cách sử dụng tổ hợp INDEX & MATCH để thực hiện tra cứu trong Excel và Google Trang tính.
INDEX & MATCH, Cặp đôi hoàn hảo
Hãy xem xét kỹ hơn một số cách bạn có thể kết hợp các hàm INDEX và MATCH. Hàm MATCH được thiết kế để trả về vị trí tương đối của một mục trong một mảng, trong khi hàm INDEX có thể lấy một mục từ một mảng cho một vị trí cụ thể. Sức mạnh tổng hợp này giữa cả hai cho phép chúng thực hiện hầu hết mọi loại tra cứu mà bạn có thể cần.
Trước đây, tổ hợp INDEX / MATCH đã được sử dụng để thay thế cho Hàm VLOOKUP. Một trong những lý do chính là khả năng thực hiện tra cứu bên trái (xem phần tiếp theo).
Lưu ý: Hàm XLOOKUP mới hiện có thể thực hiện tra cứu bên trái.
Tra cứu bên trái
Hãy sử dụng bảng thống kê bóng rổ này:
Chúng tôi muốn tìm số Người chơi của Bob. Vì # Người chơi nằm ở bên trái của cột tên, chúng tôi không thể sử dụng hàm VLOOKUP.
Thay vào đó, chúng tôi có thể thực hiện một yêu cầu MATCH cơ bản để tính toán hàng của Bob
= MATCH (H2, B2: B5, 0)
Điều này sẽ tìm kiếm một kết hợp chính xác của từ “Bob” và vì vậy hàm của chúng tôi sẽ trả về số 2, vì “Bob” nằm trong số 2NS Chức vụ.
Tiếp theo, chúng ta có thể sử dụng Hàm INDEX để trả về Số người chơi, tương ứng với một hàng. Hiện tại, chúng ta chỉ cần nhập thủ công “2” vào hàm:
= INDEX (A2: A5, 2)
Ở đây, INDEX sẽ tham chiếu A3, vì đó là 2NS ô trong phạm vi A2: A5 và trả về kết quả là 42. Đối với mục tiêu tổng thể của chúng tôi, sau đó chúng tôi có thể kết hợp hai ô này thành:
= INDEX (A2: A5, MATCH (H2, B2: B5, 0))
Lợi ích ở đây là chúng tôi có thể trả về một kết quả từ một cột bên trái nơi chúng tôi đang tìm kiếm.
Tra cứu hai chiều
Hãy nhìn vào bảng của chúng ta từ trước:
Tuy nhiên, lần này, chúng tôi muốn tìm nạp một thống kê cụ thể. Chúng tôi đã thông báo rằng chúng tôi muốn tìm kiếm Rebounds trong ô H1. Thay vì phải viết nhiều câu lệnh IF để xác định cột nào sẽ lấy kết quả, bạn có thể sử dụng lại hàm MATCH. Hàm INDEX cho phép bạn chỉ định giá trị hàng và giá trị cột. Chúng tôi sẽ thêm một hàm MATCH khác ở đây để xác định cột nào chúng tôi muốn. Điều đó sẽ giống như
= TRẬN ĐẤU (H1, A1: E1, 0)
Ô của chúng tôi trong H1 là một danh sách thả xuống cho phép chúng tôi chọn danh mục mà chúng tôi muốn tìm kiếm và sau đó TRẬN ĐẤU của chúng tôi xác định cột nào trong bảng thuộc về. Hãy gắn chút mới này vào công thức trước đây của chúng tôi. Lưu ý rằng chúng ta cần điều chỉnh đối số đầu tiên thành hai thứ nguyên, vì chúng ta không chỉ muốn một kết quả từ cột A.
= INDEX (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
Trong ví dụ của chúng tôi, chúng tôi muốn tìm Rebounds cho Charlie. Công thức của chúng tôi sẽ đánh giá điều này như sau:
= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Rebounds", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Giờ đây, chúng tôi đã tạo một thiết lập linh hoạt cho phép người dùng tìm nạp bất kỳ giá trị nào họ muốn từ bảng của chúng tôi mà không cần phải viết nhiều công thức hoặc câu lệnh IF phân nhánh.
Nhiều phần
Nó không thường được sử dụng, nhưng INDEX có đối số thứ năm có thể được đưa ra để xác định khu vực trong đối số một để sử dụng. Điều này có nghĩa là chúng ta cần một cách để chuyển nhiều vùng vào đối số đầu tiên. Bạn có thể làm điều này bằng cách sử dụng thêm một tập hợp các dấu ngoặc đơn. Ví dụ này sẽ minh họa cách bạn có thể tìm nạp kết quả từ các bảng khác nhau trên một trang tính bằng cách sử dụng INDEX.
Đây là bố cục mà chúng tôi sẽ sử dụng. Chúng tôi đã có số liệu thống kê cho ba phần tư trận đấu khác nhau.
Trong các ô H1: H3, chúng tôi đã tạo danh sách thả xuống Xác thực dữ liệu cho các lựa chọn khác nhau của chúng tôi. Danh sách thả xuống cho Quý đến từ J2: J4. Chúng tôi sẽ sử dụng điều này cho một câu lệnh MATCH khác, để xác định khu vực nào sẽ sử dụng. Công thức của chúng tôi trong H4 sẽ giống như sau:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Chúng ta đã thảo luận về cách hoạt động của hai hàm MATCH bên trong, vì vậy hãy tập trung vào đối số đầu tiên và đối số cuối cùng:
= INDEX ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Chúng tôi đã cung cấp cho hàm INDEX nhiều mảng trong đối số đầu tiên bằng cách đặt tất cả chúng trong dấu ngoặc đơn. Cách khác mà bạn có thể làm là sử dụng Công thức - Xác định Tên. Bạn có thể xác định tên gọi là “MyTables” với định nghĩa
= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Hãy quay lại toàn bộ tuyên bố. Các hàm MATCH khác nhau của chúng tôi sẽ cho biết chính xác hàm INDEX cần tìm ở đâu. Đầu tiên, chúng tôi sẽ xác định rằng “Charlie” là 3rd hàng ngang. Tiếp theo, chúng tôi muốn "Rebounds", là 4NS cột. Cuối cùng, chúng tôi đã xác định rằng chúng tôi muốn kết quả từ 2NS bàn. Công thức sẽ đánh giá thông qua điều này như sau:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEX ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEX (A10: E13, 3, 4) = D13 = 14
Như chúng tôi đã đề cập ở phần đầu của ví dụ này, bạn bị hạn chế để các bảng nằm trên cùng một trang tính. Nếu bạn có thể viết ra các cách chính xác để cho INDEX biết hàng, cột và / hoặc khu vực bạn muốn truy xuất dữ liệu từ đó, INDEX sẽ phục vụ bạn rất tốt.
Google Trang tính -INDEX & MATCH
Tất cả các ví dụ trên đều hoạt động hoàn toàn giống nhau trong Google Trang tính cũng như trong Excel.
