seychellesartprojects.org

Kết hợp chỉ mục nhiều hàng và cột tiêu chí

Tất cả chúng ta đều sử dụng hàm VLOOKUP hàng ngày để tìm nạp dữ liệu và chúng tôi cũng nhận thức được thực tế là hàm VLOOKUP có thể tìm nạp dữ liệu từ trái sang phải, vì vậy giá trị tra cứu phải luôn ở bên trái của các cột kết quả. Tuy nhiên, chúng tôi có một số lựa chọn thay thế có thể được sử dụng thay thế cho hàm VLOOKUP trong excel. Với công nghệ tiên tiến, chúng ta có thể sử dụng công thức INDEX + MATCH này để so khớp nhiều tiêu chí cho các hàng và cột. Vì vậy, bài viết đặc biệt này sẽ đưa bạn đi tìm hiểu chi tiết về kỹ thuật này.

Làm thế nào để Sử dụng Công thức INDEX + MATCH để Đối sánh Nhiều Tiêu chí?

Ở đây chúng tôi giải thích cách sử dụng công thức chỉ mục + đối sánh để đối sánh nhiều tiêu chí cho các hàng và cột với các ví dụ.

Bạn có thể tải xuống Mẫu Excel Khớp Chỉ mục Nhiều Tiêu chí này tại đây - Mẫu Excel Khớp Chỉ mục Nhiều Tiêu chí

Ví dụ # 1 - Công thức INDEX + MATCH

Không phải phần lớn các hàm tra cứu của người dùng excel nằm ngoài hàm VLOOKUP, lý do có thể là rất nhiều. Dù sao chúng ta hãy giới thiệu đơn giản về công thức này trước khi chúng ta chuyển sang cấp độ nâng cao.

Ví dụ, hãy xem cấu trúc dữ liệu dưới đây trong excel.

Chúng tôi có tên "Đại diện bán hàng" và giá trị bán hàng tương ứng của họ. Mặt khác, chúng tôi có một danh sách thả xuống “Đại diện bán hàng” trong ô D2.

Dựa trên lựa chọn mà chúng tôi thực hiện từ số tiền bán hàng trong danh sách thả xuống phải xuất hiện trong ô E2.

Vấn đề là chúng tôi không thể áp dụng công thức VLOOKUP vì giá trị tra cứu “Đại diện bán hàng” nằm ở bên phải cột kết quả “Doanh số”, vì vậy trong những trường hợp này, chúng tôi có thể sử dụng công thức giá trị tra cứu kết hợp INDEX + MATCH.

INDEX tìm kiếm giá trị số hàng được đề cập trong phạm vi A2: A11 và trong phạm vi này, chúng tôi cần cung cấp hàng mà chúng tôi cần giá trị bán hàng đến từ. Giá trị hàng này dựa trên tên "Đại diện bán hàng" được chọn trong danh sách thả xuống trong excel, do đó, hàm MATCH tìm kiếm số hàng "Đại diện bán hàng" trong phạm vi B2: B11 và trả về số hàng của giá trị phù hợp .

Ví dụ # 2 - Nhiều tiêu chí trong Công thức INDEX + MATCH

Bây giờ chúng ta có một cấu trúc dữ liệu như bên dưới.

Chúng tôi có giá trị bán hàng hàng tháng là "Đại diện bán hàng". Từ bảng này, chúng tôi cần các kết quả động như trong ô A15, tôi đã tạo danh sách thả xuống “Đại diện bán hàng” và trong ô B14, tôi đã tạo danh sách thả xuống “Tháng”.

Dựa trên lựa chọn được thực hiện trong hai ô này, công thức của chúng tôi phải tìm nạp dữ liệu từ bảng trên.

Ví dụ: nếu tôi chọn “Đại diện 8” và “Tháng 4” thì nó phải hiển thị giá trị bán hàng của “Đại diện 8” cho tháng “Tháng 4”.

Vì vậy, trong những trường hợp này, chúng ta cần phải khớp cả hàng và cột. Thực hiện theo các bước dưới đây để áp dụng công thức khớp cả hàng và cột.

Bước 1: Mở hàm INDEX trong ô B15.

Bước 2: Đối số đầu tiên của hàm INDEX là “Array” tức là từ phạm vi ô mà chúng ta cần kết quả. Vì vậy, trong trường hợp này, chúng tôi cần các giá trị bán hàng vì vậy hãy chọn phạm vi ô từ B2 đến G11.

Bước 3: Đối số tiếp theo của hàm INDEX từ hàng nào của dải ô đã chọn mà chúng ta cần kết quả. Trong trường hợp này, chúng ta cần đến số hàng "Đại diện bán hàng" dựa trên lựa chọn được thực hiện trong ô A15 thả xuống. Vì vậy, để tìm nạp động số hàng dựa trên lựa chọn, hãy mở hàm MATCH.

Bước 4: GIÁ TRỊ NHÌN của hàm MATCH là “Đại diện bán hàng” nên chọn ô A15 làm tham chiếu.

Bước 5: Mảng Tra cứu sẽ là phạm vi tên “Đại diện bán hàng” trong bảng chính. Vì vậy, hãy chọn phạm vi là A2 đến A11.

Bước 6: Loại khớp của hàm MATCH sẽ chính xác vì vậy hãy nhập số 0 làm giá trị đối số.

Bước 7: Đối số tiếp theo của hàm INDEX là “Số cột” tức là từ phạm vi ô đã chọn từ cột mà chúng ta cần kết quả. Điều này phụ thuộc vào tháng chúng tôi chọn từ danh sách thả xuống của ô B14. Vì vậy, để lấy số cột tự động mở một hàm MATCH khác.

Bước 8: Giá trị tra cứu lần này sẽ là tên tháng nên chọn ô B14 làm tham chiếu.

Bước 9: Mảng tra cứu sẽ là phạm vi tháng của các ô trong bảng chính, tức là từ B1 đến G1.

Bước 10: Đối số cuối cùng là loại đối sánh, chọn “Đối sánh chính xác” làm tiêu chí. Đóng hai dấu ngoặc và nhấn phím enter để nhận kết quả.

Như chúng ta có thể thấy ở trên, chúng tôi đã chọn “Đại diện 6” và “Tháng 4” làm tháng và công thức của chúng tôi đã trả lại giá trị bán hàng cho tháng “Tháng 4” cho “Đại diện 6”.

Lưu ý: Ô màu vàng là tham chiếu cho bạn.

Những điều cần ghi nhớ

• Sự kết hợp giữa INDEX + MATCH có thể mạnh hơn công thức VLOOKUP.
• INDEX & MATCH có thể khớp với cả tiêu đề hàng và cột và trả về kết quả từ bảng giữa.
• MATCH có thể trả về số hàng và số cột của tiêu đề bảng của cả hàng và cột.