INDEX và MATCH là hàm phổ biến trong Excel để thực hiện tra cứu nâng cao. Tại sao Thuthuatwiki lại nói là phổ biến? Là vì hàm INDEX và MATCH cực kỳ linh hoạt, bạn có thể thực hiện tra cứu theo chiều ngang và dọc, tra cứu 2 chiều, tra cứu bên trái, tra cứu phân biệt chữ hoa chữ thường và thậm chí tra cứu dựa trên nhiều điều kiện.
Bài viết này sẽ giải thích về cách kết hợp hàm INDEX và MATCH cùng nhau để thực hiện tra cứu. Phương pháp áp dụng từng bước, đầu tiên là tìm hiểu về hàm INDEX, sau đó là hàm MATCH, và hướng dẫn cho bạn cách kết hợp hai hàm với nhau để tạo ra một tra cứu hai chiều động. Có nhiều ví dụ nâng cao hơn ở cuối trang.
Xem thêm:
Nắm vững các phím tắt Excel: Tăng hiệu quả công suất làm việc
Hàm Large – Cách Tìm Giá Trị Lớn Thứ Hai Trong Excel
Mẫu bảng chấm công theo giờ tự động Excel [Miễn phí mới nhất]
Cách dùng hàm INDEX
Hàm INDEX sẽ trả về một giá trị nhất định trong một phạm vi dựa trên số hàng và số cột mà bạn chỉ định. Cú pháp của hàm INDEX rất đơn giản:
=INDEX(array, row_num, [column_num])
Trong đó:
array: Phạm vi chứa dữ liệu mà bạn muốn trả về một giá trị. Phạm vi này là bắt buộc trong hàm.row_num: Là số thứ tự hàng trong phạm vi chứa giá trị trả về. Row_num có thể được bỏ qua nếu mảng chỉ có một hàng. Nếurow_numbị bỏ qua,column_numlà bắt buộc.column_num: Là số thứ tự cột trong phạm vi chứa giá trị trả về (mặc định số thứ tự là 1 nếu ta không điền giá trị). Nếucolumn_numbị bỏ qua, thìrow_numsẽ là bắt buộc.
Dưới đây là một số ví dụ cơ bản về hàm INDEX:
Trường hợp 1: Giả sử bạn có một bảng danh sách các cuốn sách muốn mua (xem bên dưới) và bạn muốn lấy tên của cuốn sách thứ 4, bằng một công thức. Bạn có thể sử dụng hàm INDEX như sau:
=INDEX(B3:B12,4)
Trường hợp 2: Nếu bạn muốn lấy địa chỉ của cuốn sách thứ 4 bằng hàm INDEX thì sao? Trong trường hợp đó, chúng tôi có thể cung cấp cả số hàng và số cột, đồng thời cung cấp phạm vi dữ liệu lớn hơn. Công thức INDEX bên dưới sử dụng toàn bộ phạm vi dữ liệu trong B3:D12, với số hàng là 4 và số cột là 2:
=INDEX(B3:D12,4,2)
Tóm lại, INDEX nhận giá trị tại một vị trí nhất định trong một phạm vi ô dựa trên vị trí số. Khi phạm vi là một chiều, bạn chỉ cần cung cấp số hàng. Khi phạm vi là hai chiều, bạn sẽ cần cung cấp cả số hàng và số cột.
Tuy nhiên, khi làm việc với bảng dữ liệu lớn, bạn khó có thể xác định vị trí của những thứ chúng ta đang tìm kiếm, đó là lúc hàm MATCH phát huy tác dụng.
Xem thêm: Nắm vững các phím tắt Excel: Tăng hiệu quả công suất làm việc
Cách dùng hàm MATCH
Hàm MATCH sẽ trả về vị trí của một ô trong một hàng hoặc cột. Cú pháp như sau:
=MATCH(lookup_value,lookup_array,[match_type])
Trong đó:
Lookup_valueLà giá trị đã biết mà bạn sẽ sử dụng để tiến hành tra cứu hoặc tìm kiếm. Đây có thể là một giá trị hoặc tham chiếu ô.Lookup_arrayLà phạm vi ô nơi giá trị tra cứu sẽ được tìm thấy.Match_typeLà xác định loại tìm kiếm:- 0: Tìm kiếm chính xác
- 1: Kết quả gần đúng, với giá trị gần nhất nhỏ hơn giá trị tra cứu (lookup_value). Yêu cầu sắp xếp mảng tra cứu theo thứ tự tăng dần.
- -1: Kết quả gần đúng, với giá trị gần nhất lớn hơn giá trị tra cứu (lookup_value). Yêu cầu sắp xếp mảng tra cứu theo thứ tự giảm dần.
Dưới đây là một số ví dụ cơ bản về hàm MATCH:
Ví dụ: Chúng ta có thể sử dụng hàm MATCH để lấy vị trí cuốn sách “Lý Thuyết Trò Chơi” trong danh sách bên dưới:
=MATCH("lý thuyết trò chơi",B3:B12,0)
Hàm MATCH không quan tâm phạm vi nằm ngang hay dọc, như bạn có thể thấy bên dưới:
Xem thêm: Lỗi #VALUE trong Excel: nguyên nhân và 7 cách khắc phục chi tiết
Tra cứu linh hoạt với hàm INDEX kết hợp hàm MATCH
Cú pháp khi kết hợp hàm INDEX và MATCH để tra cứu linh hoạt, dễ nhớ như sau:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Trong đó:
return_range: Phạm vi chứa dãy dữ liệu bạn muốn trả về giá trị tương ứng.lookup_value: Giá trị bạn muốn tìm kiếm.lookup_range: Phạm vi chứa dãy dữ liệu bạn muốn tìm kiếm giá trị.0trong hàm MATCH đảm bảo tìm kiếm chính xác.
Ví dụ tra cứu đơn giản bằng cách kết hợp INDEX và MATCH:
Ta có bảng dữ liệu như ảnh dưới.
Từ bảng dữ liệu này, tôi muốn tìm số lượng của Chuối, dưới đây là công thức:
=INDEX($A$2:$B$5,MATCH("Chuối",$A$2:$A$5,0),2)
Bây giờ, nếu bạn đang nghĩ rằng việc này có thể dễ dàng thực hiện được bằng cách sử dụng hàm VLOOKUP thì bạn đã đúng! Đây không phải là cách sử dụng tốt nhất của INDEX và MATCH.
Ví dụ này cũng có thể được thực hiện dễ dàng bằng hàm VLOOKUP là để cho bạn thấy cách hoạt động của INDEX và MATCH trong một ứng dụng đơn giản.
Bây giờ tôi sẽ chỉ ra ứng dụng thực sự của INDEX và MATCH.
Giả sử bạn có cùng một dữ liệu, nhưng thay vì xếp dữ liệu theo cột, bạn có dữ liệu theo hàng (như hiển thị bên dưới).
Ta vẫn có thể sử dụng cách kết hợp INDEX và MATCH để lấy số lượng của “Chuối”. Dưới đây là công thức sẽ cho bạn kết quả:
=INDEX($B$1:$E$2,2,MATCH("chuối",$B$1:$E$1,0))
Lưu ý rằng bạn cần thay đổi phạm vi và chuyển đổi các phần hàng/cột để công thức này cũng hoạt động với dữ liệu ngang.
Phương pháp này không thể thực hiện được bằng hàm VLOOKUP nhưng bạn vẫn có thể thực hiện điều này một cách dễ dàng với HLOOKUP.
Sự kết hợp 2 hàm INDEX và MATCH có thể dễ dàng xử lý dữ liệu theo chiều ngang cũng như chiều dọc.
Trường hợp 2: Ta có bảng hiển thị danh sách nhân viên bán hàng và số tiền bán hàng trong ba tháng: Tháng Một, Tháng Hai và Tháng Ba. Làm cách nào để trả về số tiền bán hàng tháng 2 của “Trâm”.
Ta sử dụng công thức:
=INDEX(C3:E12,MATCH(H2,B3:B12,0),2)
Trong đó:
C3:E12là phạm vi ô mà bạn muốn truy tìm kết quả.H2là giá trị bạn muốn tìm, nằm ở ô H2.B3:B12là phạm vi mà Excel sẽ tìm kiếm giá trị trong H2.0ở cuối có nghĩa cần phải có kết quả khớp chính xác.2ở cuối hàmINDEXchỉ định số cột (cột thứ 2 trong phạm vi đã chỉ định).
Kết hợp tất cả lại với nhau, công thức về cơ bản có nội dung: “Tìm giá trị trong cột thứ 2 của phạm vi C3:E12, trong đó giá trị tương ứng trong B3:B12 khớp với giá trị trong ô H2.” Kết quả là giá trị tại giao điểm của hàng với giá trị trùng khớp và cột thứ 2 trong phạm vi đã chỉ định.
Tra cứu dữ liệu hai chiều với INDEX và MATCH
Ở trên, chúng ta đã sử dụng hàm MATCH để tìm số hàng một cách linh hoạt nhưng chỉ định cứng số cột. Vậy làm cách nào để có thể làm cho công thức hoàn toàn linh hoạt để có thể trả lại số tiền bán hàng cho bất kỳ nhân viên nào trong bất kỳ tháng nào? Đó chính là là sử dụng hàm MATCH hai lần – một lần để lấy vị trí hàng và một lần để lấy vị trí cột.
Công thức tôi sử dụng trong bảng dữ liệu là:
=INDEX(C3:E12,MATCH(H2,B3:B12,0),MATCH(H3,C2:E2,0))
Lưu ý: bạn có thể sử dụng Data Validation để tạo menu thả xuống để chọn nhân viên bán hàng và tháng.
Tra cứu dữ liệu nằm bên trái với INDEX và MATCH
Một trong những ưu điểm chính của hàm INDEX và MATCH so với hàm VLOOKUP là khả năng thực hiện “tra cứu dữ liệu nằm bên trái”.
Ví dụ: Ta có bảng dữ liệu như ảnh dưới, làm cách nào để biết doanh số bán hàng của nhân viên tên “Lâm“.
Nếu bạn đang nghĩ đến VLOOKUP thì bạn đã sai, hàm VLOOKUP không thể tìm kiếm và lấy các giá trị ở bên trái. Vì vậy, nếu bạn muốn tra cứu và truy xuất dữ liệu từ các cột bên trái, bạn nên sử dụng kết hợp hàm INDEX và MATCH.
Dưới đây là công thức tính doanh số bán hàng của Lâm:
=INDEX($A$2:$C$11,MATCH("Lâm",C2:C11,0),2)
Một điểm khác biệt ở đây giữa hàm INDEX và MATCH và hàm VLOOKUP đó là VLOOKUP chỉ có thể tìm nạp dữ liệu từ các cột ở bên phải cột có giá trị tra cứu.
Câu hỏi thường gặp
Tại sao nên sử dụng hàm INDEX và MATCH trong Excel?
INDEX và MATCH phổ biến hơn trong Excel vì nó giải quyết được hạn chế của hàm VLOOKUP và dễ sử dụng hơn. Hàm INDEX & MATCH trong Excel có một số ưu điểm so với hàm VLOOKUP:
- INDEX và MATCH tra cứu linh hoạt hơn và nhanh hơn Vlookup
- Có thể thực hiện tra cứu ngang, tra cứu dọc, tra cứu 2 chiều, tra cứu bên trái, tra cứu phân biệt chữ hoa chữ thường và có thể tra cứu dựa trên nhiều điều kiện.
- Trong bảng một dữ liệu lớn, kết hợp hai hàm này lồng nhau sẽ linh hoạt hơn VLOOKUP và có thể mang lại kết quả nhanh hơn.
INDEX và MATCH khác gì so với VLOOKUP và HLOOKUP?
INDEX và MATCH cung cấp khả năng linh hoạt hơn và có thể thực hiện tìm kiếm ở cả hàng và cột. VLOOKUP chỉ thực hiện tìm kiếm theo hàng, trong khi HLOOKUP chỉ thực hiện tìm kiếm theo cột.