INDEX và MATCH với nhiều mảng
Giả sử bạn có một số bảng có chú thích giống nhau như được hiển thị bên dưới, để tra cứu các giá trị phù hợp với tiêu chí đưa ra từ các bảng này có thể là một công việc khó khăn cho bạn. Trong hướng dẫn này, chúng ta sẽ nói về cách tra cứu giá trị trên nhiều mảng, phạm vi hoặc nhóm bằng cách khớp các tiêu chí cụ thể với INDEX, Đội hình thi đấu và CHỌN chức năng.
Làm thế nào để tra cứu một giá trị trên nhiều mảng?
Để biết lãnh đạo của các nhóm khác nhau thuộc các phòng ban khác nhau, trước tiên bạn có thể sử dụng chức năng CHOOSE để nhắm mục tiêu bảng để trả về tên của người lãnh đạo từ đó. Sau đó, hàm MATCH sẽ tìm ra vị trí của người đứng đầu trong bảng mà người đó thuộc về. Cuối cùng, hàm INDEX sẽ truy xuất người lãnh đạo dựa trên thông tin vị trí cộng với cột cụ thể nơi tên của người lãnh đạo được liệt kê.
Cú pháp chung
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- mảng_num: Số CHOOSE được sử dụng để chỉ ra một mảng từ danh sách array1, array2,… để trả về kết quả từ.
- array1, array2,…: Các mảng để trả về kết quả từ đó. Ở đây đề cập đến ba bảng.
- tra cứu_giá trị: Giá trị mà công thức kết hợp sử dụng để tìm vị trí của nhà lãnh đạo tương ứng. Ở đây đề cập đến nhóm đã cho.
- tra cứu_mảng: Phạm vi của các ô trong đó lookup_value đã được liệt kê. Ở đây đề cập đến phạm vi nhóm. Lưu ý: Bạn có thể sử dụng phạm vi nhóm từ bất kỳ phòng ban nào vì chúng đều giống nhau và chúng tôi chỉ cần lấy số vị trí.
- cột_num: Cột bạn chỉ ra mà bạn muốn truy xuất dữ liệu.
Để biết lãnh đạo nhóm D thuộc Cục A, vui lòng sao chép hoặc nhập công thức bên dưới vào ô G5 và nhấn đăng ký hạng mục thi để nhận được kết quả:
= INDEX (CHỌN (1,$ B $ 5: $ C $ 8,$ B $ 11: $ C $ 14,$ B $ 17: $ C $ 20),TRẬN ĐẤU(F5,$ B $ 5: $ B $ 8, 0),2)
√ Lưu ý: Các dấu đô la ($) ở trên biểu thị các tham chiếu tuyệt đối, có nghĩa là tên và phạm vi lớp trong công thức sẽ không thay đổi khi bạn di chuyển hoặc sao chép công thức sang các ô khác. Sau khi bạn nhập công thức, hãy kéo chốt điền xuống để áp dụng công thức cho các ô bên dưới, sau đó thay đổi mảng_num cho phù hợp.
Giải thích công thức
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Hàm CHOOSE trả về 1mảng st từ ba mảng được liệt kê trong công thức. Vì vậy, nó sẽ trở lại $ B $ 5: $ C $ 8, tức là phạm vi dữ liệu của Cục A.
- TRẬN ĐẤU (F5, $ B $ 5: $ B $ 8,0): Match_type 0 buộc hàm MATCH trả về vị trí của kết quả khớp đầu tiên của Nhóm D, giá trị trong ô F5, trong mảng $ B $ 5: $ B $ 8, Đó là 4.
- MỤC LỤC(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),TRẬN ĐẤU (F5, $ B $ 5: $ B $ 8,0),2) = INDEX ($ B $ 5: $ C $ 8,4,2): Hàm INDEX truy xuất giá trị tại giao điểm của 4hàng thứ và 2cột thứ n của phạm vi $ B $ 5: $ C $ 8, Đó là Emily.
Để tránh thay đổi mảng_num trong công thức mỗi khi bạn sao chép nó, bạn có thể sử dụng cột trợ giúp, cột D. Công thức sẽ như sau:
= INDEX (CHỌN (D5,$ B $ 5: $ C $ 8,$ B $ 11: $ C $ 14,$ B $ 17: $ C $ 20),TRẬN ĐẤU(F5,$ B $ 5: $ B $ 8, 0),2)
√ Lưu ý: Những con số 1, 2, 3 trong cột trợ giúp chỉ ra array1, array2, array3 bên trong chức năng CHOOSE.
Các chức năng liên quan
Hàm INDEX trong Excel trả về giá trị được hiển thị dựa trên một vị trí nhất định từ một phạm vi hoặc một mảng.
Hàm MATCH trong Excel tìm kiếm một giá trị cụ thể trong một phạm vi ô và trả về vị trí tương đối của giá trị.
Hàm CHOOSE trả về một giá trị từ danh sách đối số giá trị dựa trên số chỉ mục đã cho. Ví dụ: CHOOSE (3, "Apple", "Peach", "Orange") trả về màu cam, số chỉ mục là 3 và màu cam là giá trị thứ ba sau số chỉ mục trong hàm.
Công thức liên quan
Tra cứu giá trị từ một trang tính hoặc sổ làm việc khác
Nếu bạn biết cách sử dụng hàm VLOOKUP để tìm kiếm các giá trị trong một trang tính, thì các giá trị vlookup từ một trang tính hoặc sổ làm việc khác sẽ không phải là vấn đề đối với bạn.
Vlookup với tên trang tính dymanic
Trong nhiều trường hợp, bạn có thể cần thu thập dữ liệu trên nhiều trang tính để tóm tắt. Với sự kết hợp của hàm VLOOKUP và hàm INDIRECT, bạn có thể tạo công thức để tra cứu các giá trị cụ thể trên các trang tính với tên trang tính động.
Tra cứu nhiều tiêu chí với INDEX và MATCH
Khi xử lý một cơ sở dữ liệu lớn trong bảng tính Excel với một số cột và chú thích hàng, luôn luôn khó khăn để tìm ra thứ gì đó đáp ứng nhiều tiêu chí. Trong trường hợp này, bạn có thể sử dụng công thức mảng với các hàm INDEX và MATCH.
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel - Giúp bạn nổi bật giữa đám đông
Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...
Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)
- Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
- Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
- Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
- Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.
