INDEX MATCH với nhiều tiêu chí cho phép bạn thực hiện tra cứu thành công khi có nhiều giá trị tra cứu khớp
Nói cách khác, bạn có thể tra cứu và trả về các giá trị ngay cả khi không có giá trị duy nhất nào để tìm kiếm
Điều này không thể đạt được với bất kỳ công thức tra cứu nào khác mà không chèn cột trợ giúp😲
Thực hiện theo 3 bước đơn giản sau để tạo INDEX MATCH của riêng bạn với nhiều tiêu chí trong vài phút
Nếu bạn muốn tag cùng thì tải file Excel mẫu tại đây
Mục lục
Ví dụ về INDEX MATCH với nhiều tiêu chí
Vì vậy, bạn đã có cơ sở dữ liệu nhân viên này
Bạn muốn làm cho cơ sở dữ liệu dễ tìm kiếm hơn, vì vậy bạn đang tạo một công cụ nhỏ [ở bên phải]
Trong công cụ đó, bất kỳ ai cũng có thể nhập tên và bộ phận của một nhân viên và nó sẽ tìm thấy mức lương của người đó [và hiển thị nó trong ô G4]
“Thật dễ dàng, tôi chỉ cần sử dụng VLOOKUP. ”
Đợi chút✋
Thật không may, nó không phải là dễ dàng
Bạn thấy đấy, vấn đề là thực sự có 2 nhân viên được gọi là “Steve Jones”. Điều đó có nghĩa là giá trị tra cứu có 2 giá trị khớp trong cột tra cứu
Không có gì độc đáo về tên của Steve Jones
Trong thuật ngữ Excel, 'Tên' sẽ là 1 tiêu chí
Vì vậy, 1 tiêu chí đã không cắt nó
Nhưng nếu bạn bao gồm một tiêu chí khác, chẳng hạn như 'Bộ phận', bạn sẽ khiến Steve Jones trở nên độc nhất
Bây giờ, trong khi “Steve Jones” xuất hiện nhiều lần trong danh sách, thì chỉ có một “Steve Jones từ bộ phận bán hàng”
Đây là loại phép thuật bạn có thể làm với INDEX MATCH với nhiều tiêu chí
Bước 1. Chèn công thức INDEX MATCH bình thường
INDEX MATCH với nhiều tiêu chí là một ‘công thức mảng’ được tạo từ hàm INDEX và MATCH
Công thức mảng có cú pháp khác với công thức thông thường. Về cơ bản, nó là một công thức bình thường trên steroid💪
Kasper Langmann, Chuyên gia Microsoft Office
Sự phối hợp giữa các hàm INDEX và MATCH là
- MATCH tìm kiếm một giá trị và trả về một vị trí
- MATCH cung cấp vị trí cho hàm INDEX
- Sau đó, INDEX chuyển đổi vị trí này thành kết quả
Bắt đầu công thức
Trước tiên, hãy bắt đầu hàm MATCH, sau đó đặt hàm INDEX xung quanh hàm MATCH để hoàn thành công thức
Bắt đầu với
=TRẬN ĐẤU[
1. Là đối số đầu tiên trong hàm MATCH, hãy nhập giá trị tra cứu. Đây là thứ bạn đang tìm kiếm.
Trong trường hợp này, bạn đang tìm kiếm một nhân viên có tên “Steve Jones”
Chọn [hoặc nhập thủ công] ô G2 làm giá trị tra cứu, sau đó phân tách bằng dấu phẩy để chuyển sang mảng tra cứu
2. Mảng tra cứu là cột mà hàm MATCH tìm kiếm giá trị tra cứu
Chọn cột có tên, rồi nhập dấu phẩy để chuyển sang [match_type]
Bây giờ công thức của bạn sẽ trông như thế này
=MATCH[G2,A. MỘT,
3. Một danh sách thả xuống nhỏ xuất hiện cho bạn lựa chọn giữa 1, 0 và -1
Tùy chọn 0 là tùy chọn “đối sánh chính xác” và được sử dụng phổ biến nhất. -1 và 1 tương tự như phương pháp “so khớp gần đúng” của VLOOKUP
Viết 0 hoặc nhấp đúp vào tùy chọn '0 – Khớp chính xác' trong menu thả xuống và nhập dấu ngoặc đơn kết thúc
Công thức của bạn bây giờ sẽ trông như thế này
=MATCH[G2,A. A,0]
4. Bọc chức năng INDEX xung quanh chức năng MATCH
Bây giờ công thức của bạn sẽ trông như thế này
=INDEX[MATCH[G2,A. A,0]
Nhưng chúng ta vẫn chưa xong✋
Cú pháp của hàm INDEX đi
INDEX[mảng, số hàng, số cột]
Hàm MATCH phải là đối số thứ 2 trong cú pháp INDEX
Ngay bây giờ, đó là đối số đầu tiên
Vì vậy, hãy bắt đầu viết đối số đầu tiên thực sự. mảng
Mảng INDEX là cột mà bạn muốn trả về các giá trị từ đó
Mục đích của nhiều tiêu chí INDEX MATCH là để tìm mức lương của một nhân viên cụ thể
Vậy mảng là cột lương [cột D]
Bây giờ công thức của bạn sẽ trông như thế này
=INDEX[D. D,TRẬN ĐẤU[G2,A. A,0]
Và chỉ cần đặt thêm một dấu ngoặc đơn ở cuối để kết thúc hàm INDEX
Vì vậy, công thức cuối cùng trông như thế này
=INDEX[D. D,TRẬN ĐẤU[G2,A. A,0]]
GIẢI TRÌNH
Hàm MATCH tìm kiếm giá trị trong G2 [“Steve Jones”] trong cơ sở dữ liệu rồi trả về một số
Số này là hàng trong dữ liệu có tên “Steve Jones”
Số hàng này sau đó được đưa vào cú pháp của hàm INDEX
Vấn đề. Có nhiều hơn một nhân viên được gọi là “Steve Jones”. Chúng ta đang thực sự nhìn thấy tiền lương của ai?
Các công thức tra cứu trong Excel luôn tìm kiếm từ trên xuống dưới, vì vậy bạn đang thấy mức lương của Steve Jones trên cùng [ở hàng 3]
Đó là Steve mà bạn đang tìm kiếm
Nhưng thật may mắn khi bạn tìm thấy anh ấy 🍀
Để đảm bảo chúng tôi luôn tìm thấy Steve Jones từ việc bán hàng, hãy làm theo các bước còn lại bên dưới
Bước 2. Thay đổi giá trị tra cứu thành 1
Bây giờ, bạn cần thay đổi công thức INDEX MATCH bình thường thành công thức mảng
Nghe có vẻ khó?
Đừng lo lắng, tôi sẽ hướng dẫn bạn từng bước😊
1. Thay đổi giá trị tra cứu của hàm MATCH thành 1. Nó chỉ đơn giản như âm thanh
Vì vậy, công thức thay đổi từ
=INDEX[D. D,TRẬN ĐẤU[G2,A. A,0]]
Đến
=INDEX[D. D,TRẬN ĐẤU[1,A. A,0]]
“Lý thuyết” đằng sau điều này không đơn giản như việc thay đổi giá trị tra cứu
Vì bạn đang thay đổi công thức từ công thức bình thường sang công thức mảng nên cấu trúc của công thức cũng thay đổi một chút. Bằng cách thay đổi giá trị tra cứu thành 1, bạn không thực sự yêu cầu hàm MATCH tìm kiếm số 1 trong mảng tra cứu [cột họ]
Trong ngôn ngữ Excel, 1 có nghĩa là TRUE. 0 có nghĩa là SAI
Kasper Langmann, Chuyên gia Microsoft Office
Khi bạn nhập hai tiêu chí của chúng tôi trong bước tiếp theo, số 1 trong hàm MATCH chỉ có nghĩa là
“Xem qua các hàng trong dữ liệu và trả về số hàng có tất cả các tiêu chí là TRUE”
Nếu bạn viết số 0, công thức sẽ tìm kiếm một hàng mà tất cả tiêu chí của chúng tôi là SAI – và điều đó sẽ không thực sự .
Bước 3. Viết các tiêu chí
Tiêu chí thay thế đối số thứ 2 của hàm MATCH, với cấu trúc này
[phạm vi=tiêu chí1]*[phạm vi=tiêu chí2]*[phạm vi=tiêu chí3]*…
Bằng cách này, bạn có thể có nhiều tiêu chí như bạn cần
INDEX MATCH với 2 tiêu chí
Thông thường, chỉ cần sử dụng 2 tiêu chí để làm cho giá trị tra cứu của bạn là duy nhất
Tiêu chí 1 = tên
Tiêu chí 2 = chia
Hãy xem liệu bạn có thể tìm thấy “Steve Jones từ việc bán hàng” hay liệu anh ấy có bị lạc trong rừng không🌳
Thay thế cấu trúc trên bằng các tiêu chí thực tế
[phạm vi=tiêu chí1]*[phạm vi=tiêu chí2]
[MỘT. A=G2]*[B. B=G3]
A. A là cột có tên. G2 là cái tên bạn đang tìm kiếm
B. B là cột có phép chia. G3 là bộ phận bạn đang tìm kiếm
Viết nó làm đối số thứ 2 trong hàm MATCH, thay thế những gì hiện có
Công thức của bạn bây giờ sẽ trông như thế này
=INDEX[D. D,TRẬN ĐẤU[1,[A. A=G2]*[B. B=G3],0]]
Có vẻ lạ khi gõ dấu ngoặc đơn ngẫu nhiên vào công thức, nhưng đây là cách bạn cấu trúc các tiêu chí để công thức mảng hiểu được điều đó
Kasper Langmann, Chuyên gia Microsoft Office
Nếu bạn đang sử dụng Microsoft 365 chỉ cần nhấn Enter và xem tra cứu nhiều tiêu chí tuyệt đẹp của bạn💡
Giải trình. Ctrl + Shift + Enter
Nếu bạn không sử dụng Microsoft 365, đừng không nhấn Enter khi bạn hoàn thành công thức. Nó sẽ không hoạt động.
Thay vào đó, nhấn và giữ Ctrl và Shift rồi nhấn Enter
Cảnh báo. {dấu ngoặc nhọn} sẽ xuất hiện xung quanh công thức của bạn. Họ được cho là ở đó
Mỗi khi bạn thay đổi công thức này, bạn phải kết thúc bằng Ctrl + Shift + Enter
[Thay vì chỉ “Enter” thông thường như bạn có thể đã quen]
Đó là nó - Bây giờ thì sao?
Ồ… Bạn vừa học cách sử dụng INDEX MATCH với nhiều tiêu chí…
Rốt cuộc nó không quá đáng sợ, phải không?
Bây giờ, bạn đã tạo một công cụ để dễ dàng tra cứu nhân viên và trả lương cho họ – ngay cả khi có nhiều nhân viên có cùng tên.
Tất cả được thúc đẩy bởi các chức năng INDEX và MATCH
Các công thức [và hàm] tra cứu nói chung cực kỳ hữu ích và tăng tốc đáng kể công việc của bạn🏃
Nhưng tra cứu nhiều tiêu chí thực sự tiết kiệm thời gian của bạn khi giá trị tra cứu không phải là duy nhất
Nhưng nếu bạn thực sự muốn làm việc hiệu quả hơn trong Excel, bạn cần đi sâu vào macro
Macro tự động hóa các quy trình làm việc, vì vậy bạn có thể thực hiện 50 việc chỉ bằng một cú nhấp chuột
Tôi hứa với bạn, nó không khó như bạn nghĩ
Tham gia khóa học video 30 phút miễn phí của tôi và bắt đầu với macro [dành cho người mới bắt đầu]
Các tài nguyên liên quan khác
INDEX MATCH không phải là công thức tra cứu duy nhất hiện có, mặc dù đây là công thức duy nhất bạn có thể biến thành công thức mảng. Nếu bạn muốn mở rộng hộp công cụ của mình, bạn chắc chắn nên biết VLOOKUP và XLOOKUP mới
Nếu bạn nghĩ điều này khó hiểu, bạn nên đi sâu vào tìm hiểu riêng về hàm INDEX và hàm MATCH để hiểu rõ hơn về chúng