Kết hợp Hàm Index và Match là sự cộng hưởng thông minh và tiện lợi cho người sử dụng Excel vì nó mang lại sự tiết kiệm thời gian hơn cả VLOOKUP. Trong đó hàm =INDEX() trả về giá trị của một ô trong bảng tính dựa trên số cột và số hàng, hàm = MATCH() trả về vị trí của một ô trong một hàng hoặc cột. Cùng Blog ứng dụng tham khảo về sự cộng hưởng này nhé!
Bạn đang xem bài viết: Cách kết hợp hàm INDEX và MATCH trong Excel tối ưu nhất 2022
Nội dung bài viết
Hàm INDEX trong Excel là gì?
Hàm INDEX trong Excel trả về thành quả từ mảng dựa trên số thứ tự hàng và cột bạn xác định. Cú pháp hàm INDEX rất đơn giản:
INDEX(array, row_num, [column_num])
dưới đây là cách giải thích đơn giản cho mỗi thông số:
array – Nó là dải ô địa điểm bạn muốn thành quả được trả về.
row_num – số thứ tự của hàng trong mảng mà bạn mong muốn giá trị được trả về. nếu để trống, thì colum_num là bắt buộc.
colum_num – số thứ tự của cột trong mảng mà bạn mong muốn thành quả được trả về. nếu như bỏ trống, thì row_num là bắt buộc.
nếu cả chỉ số row_num và colum_num đều được sử dụng, thì hàm INDEX sẽ trả về thành quả ở ô là giao điểm của hàng và cột xác định.
Và Nó là VD dễ dàng nhất của hàm INDEX:
=INDEX(A1:C10,2,3)
bí quyết tìm kiếm dải ô A1:C10 rồi trả về giá trị của ô ở hàng thứ 2 và cột thứ ba, cụ thể là ô C2.
Rất dễ, đúng không? mặc dù vậy, khi giải quyết dữ liệu thực, bạn hầu như không biết được mình mong muốn hàng nào cột nào, Đây là nguyên nhân bạn phải cần sự tạo điều kiện của hàm MATCH.
Hàm MATCH trong Excel là gì?
Hàm MATCH trong Excel chọn lựa giá trị cần tìm trong dải ô, rồi trả về vị trí tương đối của ô đó trong dải ô.
VD, nếu như dải ô B1:B3 chứa các thành quả “New-York”, “Paris”, “London”, rồi bí quyết =MATCH(“London”,B1:B3,0) trả về số 3 bởi vì “London” là mục thứ ba trong dải ô.
Cú pháp hàm MATCH có dạng dưới đây:
MATCH(lookup_value, lookup_array, [match_type])
lookup_value – Nó là chữ sô hay chuỗi ký tự mà bạn chọn lựa. Đây có khả năng là một thành quả, một tham chiếu ô hay một giá trị lô gic.
lookup_array – dải ô được tìm kiếm.
match_type – chỉ số này nói cho hàm MATCH biết bạn muốn trả về sự phù hợp tuyệt đối hay sự ổn tương đối.
1 hay bỏ trống – tìm giá trị lớn nhất – giá trị nhỏ hơn hay bằng thành quả cần tìm. Các giá trị trong mảng cần tìm phải được lọc theo trình tự tăng dần, rõ ràng là từ nhỏ nhất đến khổng lồ nhất.
0 – tìm giá trị trước tiên bằng giá trị cần tìm. Trong sự cộng hưởng của hàm INDEX và hàm MATCH, bạn hầu như bao giờ cũng cần sự ổn tuyệt đối, bởi vậy câu lệnh thứ ba của hàm MATCH là “0”.
-1 – tìm thành quả nhỏ nhất lớn hơn hay bằng thành quả cần tìm. Các thành quả trong mảng cần tìm phải được phân bổ theo trình tự giảm dần, cụ thể là từ khổng lồ nhất đến nhỏ nhất.
Lúc đầu, tính hữu ích của hàm MATCH có khả năng không rõ ràng. Ai quan tâm đến vị trí của giá trị trong dải ô chứ? Điều ta thật sự muốn được biết đó chính là giá trị.
Bài tập 1: sử dụng hàm MATCH tìm vị trí theo dòng, cột
Cho bảng tính sau:
nếu như muốn được biết nội dung của người có tên “Lê Thị Thủy” thì bạn phai làm thế nào? Chắc hẳn việc đầu tiên bạn phải cần nắm rõ ràng coi người đó nằm ở vị trí thứ mấy trong bảng tính này.
bằng cách sử dụng hàm MATCH, bạn sẽ nhận biết 1 người nào đấy nằm ở dòng thứ mấy trong bảng.
Ta có công thức sau:
=MATCH(“Lê Thị Thủy”, cột họ tên, số 0)
- Tại ô D12 nhập tên Lê Thị Thủy
- cột họ tên là vùng C3:C8
Như vậy hàm MATCH cho kết quả là số 3, tương ứng với dòng thứ 3 trong bảng dữ liệu trên.
Tham khảo: Cách sử dụng hàm MATCH trong Excel
Vậy việc tìm ra số 3 thì có ý nghĩa gì? Hay nói cách khác biết người đấy ở vị trí dòng thứ 3 trong bảng thì có công dụng gì?
Để giải đáp câu hỏi này, con người coi tiếp yêu cầu dưới đây
Bài tập 2: kết hợp hàm INDEX và MATCH tìm mail theo tên
Trong Tin học Văn phòng, khi nhìn vào bảng trên, ta thấy tại vị trí dòng 3, trong cột mail sẽ hiển thị mail cần tìm. Như vậy có khả năng hiểu: chỉ phải dóng theo cột mail, tại vị trí dòng 3 trong bảng là chúng ta có kết quả cần tìm.
Điều đấy chính là cách kết hợp hàm INDEX với MATCH:
Trong bí quyết tại ô D15 tìm mail, chúng ta thấy:
=INDEX(E3:E8,MATCH(D12,C3:C8,0))
bí quyết này, hàm INDEX có nhiệm vụ:
- Chỉ ra cột chứa giá trị cần tìm là cột E, vùng E3:E8
- Chỉ ra dòng chứa giá trị cần tìm, dòng này đã nắm rõ ràng bởi hàm MATCH ở trên
Giao điểm giữa dòng và cột trong hàm INDEX tìm chính là kết quả mail cần tìm.
Tham khảo: 6 tính năng hiệu quả nhất của hàm INDEX trong Excel
Bài tập 3: Dò tìm từ phải qua phía trái kết hợp hàm INDEX và MATCH
bình thường chúng ta hay dò tìm theo chiều từ Trái qua phía phải. nhưng nếu như phải tìm từ Phải qua trái (ngược lại với thông thường) thì làm thế nào?
VD như sau: Tìm tên phụ thuộc vào nội dung Email?
Cột mail là cột thứ 3, nằm bên phải cột họ và tên (cột thứ 1).
Như vậy để dò tìm họ và tên phụ thuộc vào email tức là chúng ta đang tìm từ Phải qua trái.
quá trình thực hiện như sau:
- dùng hàm MATCH để tìm vị trí dòng của email trong cột mail =MATCH(Email, vùng chứa mail, 0)
- liên kết với hàm INDEX trong việc chỉ ra vị trí cột chứa kết quả cần tìm, là cột họ tên (cột C), vùng C3:C8
=INDEX(C3:C8,MATCH(D12,E3:E8,0))
Rất đơn giản đúng không nào.
Bài tập 4: Nắm rõ ràng đơn giá của mặt hàng theo nhiều điều kiện
VD ta có bảng đơn giá như sau:
Làm cách nào nắm rõ ràng được đơn giá của 1 mã hàng bất kỳ, trong 1 doanh nghiệp bất kỳ?
việc này tương đương việc chúng ta phải xét đồng thời 2 điều kiện: Mã hàng và Mã công ty
để thực hiện đòi hỏi này, chúng ta sẽ dùng hàm Index kết hợp hàm Match như sau:
Bước 1: nắm rõ ràng vị trí Mã hàng nằm trên cột thứ mấy trong bảng
- Tham chiếu thành quả Mã hàng trong dòng 3 (dòng chứa nội dung mã hàng làm căn cứ đối chiếu)
- Hàm MATCH trả về kết quả là 2, tương ứng với cột thứ 2
=MATCH(H2,A3:E3,0)
Bước 2: xác định vị trí Mã công ty nằm trên dòng thứ mấy trong bảng
- Tham chiếu giá trị Mã công ty trong cột A (cột chứa nội dung mã doanh nghiệp làm căn cứ đối chiếu)
- Hàm MATCH trả về kết quả là 3, tương ứng với dòng thứ 3
=MATCH(H3,A3:A7,0)
Như vậy giao điểm của dòng 3, cột 2 chính là ô B5 (bởi bảng bắt đầu tính từ dòng 3 trở đi, nên dòng 5 ứng với vị trí dòng thứ 3 của bảng đơn giá)
Bước 3: nắm rõ ràng kết quả đơn giá cần tìm với hàm INDEX
Việc còn lại là dùng hàm INDEX để kết nối giữa Dòng và Cột đã nắm rõ ràng được ở trên.
Trong hàm INDEX này ta có:
- Thay vì tham chiếu tới 1 cột cố định tại tham số trước tiên, con người tham chiếu cho cả vùng bảng A3:E7.
- Trong bảng này, số dòng xác định bởi hàm MATCH tại ô I3, số cột nắm rõ ràng bởi hàm MATCH tại ô I2.
- Kết quả của hàm INDEX là giao điểm giữa số dòng và số cột đã xác định được.
=INDEX(A3:E7,I3,I2)
Một vài lỗi thường gặp khi kết hợp hàm INDEX và MATCH
trong lúc sử dụng hàm INDEX Kết hợp với hàm MATCH, bạn sẽ gặp lỗi #NA và lỗi #VALUE xảy ra. Các nguyên nhân gây ra lỗi này thường là:
Lỗi #NA
- Khi hàm MATCH không tìm thấy giá trị trong phạm vi dò tìm, nó trả về thành quả #N/A.
- khi bạn sử dụng một phạm vi trong INDEX, MATCH thay vì một giá trị, bạn cần nhấn Ctrl+Shift+Enter để chuyển về công thức mảng.
- khi mà bạn sử dụng MATCH, nên có sự nhất quán giữa thành quả trong đối số match_type và tuần tự sắp xếp các giá trị trong phạm vi dò tìm, nếu như không bạn sẽ gặp thành quả #N/A.
Lỗi #VALUE khi kết hợp hàm INDEX và MATCH
nếu bạn đang dùng chỉ mục dưới dạng công thức mảng cùng với kết quả ổn để có khả năng dò tìm một giá trị, bạn có thể cần chuyển công thức của chúng ta thành công thức mảng bằng cách nhấn Ctrl+Shift+Enter, nếu không thì bạn có thể thấy lỗi #VALUE! xảy ra.
Những câu hỏi hay gặp khi kết hợp hàm INDEX và MATCH
Hàm MATCH có thể kết hợp được với hàm nào nữa không?
Ngoài việc liên kết với hàm INDEX thì hàm MATCH còn có thể liên kết với nhiều hàm khác như hàm LEFT, RIGHT, IF, COUNTIF, SUM, SUMIF, MATCH, QUERY và nhiều hơn nữa.
Hàm INDEX có khả năng kết hợp được với hàm nào nữa không?
Ngoài việc liên kết với hàm MATCH thì hàm INDEX còn có khả năng Kết hợp với nhiều hàm khác như hàm VLOOKUP, COUNTIF, SUM, IMPORTRANGE, AVERAGE.
Tổng kết
Như vậy, trên đây Blog Phần Mềm đã chỉ dẫn các bạn cách sử dụng kết hợp hàm Index và hàm Match trên bảng tính Excel để thu thập giá trị ở dòng và cột ở các bảng khác nhau và điền giá trị chính xác vào bảng dữ liệu. Bạn có thể sử dụng cách làm này trên các phiên bản Office 2013, Office 2010, Office 2007 và Office 2003.