Chữa gà mờ về Excel nhờ những ví dụ thực tế về cách sử dụng của hàm INDEX và MATCH trong Excel để trở thành người thành thạo các hàm cũng như trường dữ liệu trong phần mềm tính toán của bộ tin học văn phòng.
Các kỹ năng về tin học văn phòng là một trong những phần kiến thức không thể thiếu đối với những ai làm nhân viên văn phòng hay bất cứ người tìm việc nào hiện nay là việc liên quan đến công việc văn phòng sổ sách. Trong phần mềm tính toán excel thuộc tin học văn phòng, hàm index và hàm match được kết hợp với nhau để lấy giá trị ở các hàng và cột và trả vè kết quả tốt, giúp người dùng dễ dàng thay thế cho công thức hàm tìm kiếm Vlookup và Hlookup. Nếu bạn chưa biết về hàm index trong Excel và các thủ thuật excel liên quan đến hàm này thì hãy tìm hiểu bài viết dưới để rõ hơn nhé.
I. Hàm INDEX và MATCH trong Excel được dùng như thế nào?
Hàm INDEX và MATCH trong Excel được dùng như thế nào?
1. Công thức hàm INDEX trong Excel
Hàm INDEX trong Excel được chia ra hàm index dạng mảng và hàm index dạng tham chiếu. Hàm index dạng mảng sẽ trả về giá trị của một phần trong bảng hay mảng được chọn bởi những chỉ mục số hàng và cột. Sử dụng hàm index dạng mảng nếu đối số thứ nhất của nó là một hằng số mảng. Hàm INDEX ở dạng tham chiếu sẽ trả về tham chiếu của ô nằm ở vị trí giao của một hàng và cột cụ thể.
Hàm INDEX dạng mảng:
Cú pháp của hàm INDEX dạng mảng như sau: =INDEX(Array,Row_num,[Column_num])
Trong đó các thành phần của hàm INDEX dạng mảng:
- Array: là phạm vi ô hoặc một hằng số mảng là giá trị bắt buộc
- Row_num: hàng trong mảng được chọn mà từ đó trả về một giá trị
- Column_num: cột trong mảng được chọn mà từ đó trả về một giá trị.
Bắt buộc công thức hàm INDEX dạng mảng phải có ít nhất một trong hai đối số là Row_num hoặc Column_num.
Công thức hàm INDEX dạng tham chiếu:
Cú pháp hàm INDEX dạng tham chiếu: =INDEX(Reference,Row_num,[Column_num],[Area_num])
Trong đó các thành phần của hàm INDEX dạng tham chiếu:
- Reference: là vùng tham chiếu, đây là giá trị bắt buộc.
- Row_num: chỉ số thứ tự hàng từ đó trả về một giá trị tham chiếu, đây là giá trị bắt buộc.
- Column_num: chỉ số thứ tự cột trả về một tham chiếu, là giá trị tùy chọn.
- Area_num: Số của vùng ô trả về giá trị trong reference. Nếu thành phần Area_num được bỏ qua thì hàm INDEX trong excel dùng vùng 1, đây là giá trị tùy chọn.
2. Hàm MATCH trong Excel
Cú Pháp hàm MATCH trong Excel: =MATCH(Lookup_value,Lookup_array,[Match_type])
Trong đó các thành phần của hàm MATCH:
- Lookup_value: là giá trị tìm kiếm (có thể là giá trị số, giá trị logic, văn bản, văn bản hay giá trị logic, tham chiếu ô đến một số)
- Lookup_array: là mảng để tìm kiếm, là giá trị bắt buộc
- Match_type: là kiểu tìm kiếm, là giá trị không bắt buộc.
Có 3 kiểu tìm kiếm đó là:
- 1: Less than tìm kiếm giá trị nhỏ hơn giá trị tìm kiếm
- 0: Exact match: tìm kiếm chính xác giá trị tìm kiếm
- -1: Greater than tìm kiếm những giá trị lớn hơn giá trị tìm kiếm
Khi bỏ qua thì hàm MATCH mặc định là 1.
II. Vì sao sử dụng hàm INDEX và MATCH tốt hơn hàm VLOOKUP
1. Hàm VLOOKUP là công thức 1 chiều
Với hàm VLOOKUP người dùng chỉ có thể thực hiện tra cứu dữ liệu từ trái qua phải. Trong ví dụ dưới đây, nếu thực hiện tìm kiếm quốc gia mà có thủ đô là Seoul thì sẽ không thể dùng hàm tìm kiếm VLOOKUP để tìm ra Hàn Quốc được. Tuy nhiên, một thủ thuật excel sẽ giải quyết được bài toán như này, đó là kết hàm INDEX và MATCH trong một công thức.
Sử dụng hàm INDEX và MATCH tốt hơn hàm VLOOKUP
2. VLOOKUP sẽ bị sai nếu thêm hoặc bớt cột trong bảng tính.
Khi dùng VLOOKUP người dùng phải chỉ ra được cột nào muốn lấy giá trị trả về. Khi thêm hay bớt 1 cột ở giữa của vị trí cột đầu tiên và cột cần lấy giá trị thì cột cần lấy giá trị đó bị xê dịch đi và dẫn đến kết quả là công thức của hàm VLOOKUP không đúng nữa. Với hàm INDEX và MATCH thì điều này không bao giờ xảy ra vì khi thực hiện thêm hay bớt cột thì công thức của hàm INDEX và MATCH luôn được điều chỉnh theo.
III. Hướng dẫn cách kết hợp hàm INDEX và MATCH để thay thế hàm Vlookup trong Excel
Thủ thuật excel kết hợp hàm index và match để thay thế hàm tìm kiếm vlookup trong excel thông qua ví dụ như sau: Tìm kiếm chất liệu của mặt hàng là Tất cổ chân trong bảng dữ liệu.
Kết hợp hàm INDEX và MATCH để thay thế hàm Vlookup trong Excel
Người dùng muốn đi tìm kiếm chất liệu của tất cổ chân có trong bảng. Công thức để tìm ra kết quả như sau:
=INDEX(D1:D7,MATCH("Tất cổ chân",B1:B7,0))
Trong công thức hàm index trong excel ở trên:
- D1:D7 là cột chứa dữ liệu mà người dùng muốn tra cứu, tìm kiếm hay trích lọc
- MATCH("Tất cổ chân",B1:B7,0) sẽ cho chúng ta biết chất liệu của tất cổ chân ở vị trí hàng thứ mấy trong bảng tính trên.
Tổng quát lại thì ta có công thức hàm index trong excel:
=INDEX(vùng có chứa dữ liệu cần tra cứu,(MATCH(giá trị dùng để tra cứu,cột có chứa giá trị này, 0))
IV. Thông tin thêm về cách kết hợp hàm INDEX và MATCH
1. Phương pháp tìm kiếm từ phải qua trái với hàm INDEX + MATCH
Cho bảng dữ liệu như trong bảng. Nếu muốn tìm mặt hàng ứng với chất liệu Vải lanh trong bảng thì người dùng không thể sử dụng hàm vlookup thì hàm này chỉ tìm kiếm được vị trí từ trái qua phải. Để giải quyết bài toán thì người dùng phải sử dụng hàm INDEX + MATCH như sau:
Phương pháp tìm kiếm từ phải qua trái với hàm INDEX + MATCH
Công thức sử dụng khi này là: =INDEX(B1:B7,MATCH("Vải lanh",D1:D7,0))
2. Bài tập tìm đơn giá của mặt hàng dựa theo nhiều điều kiện
Tìm đơn giá của từng mặt hàng ứng với một giá trị mã hàng AT và mã công ty SA với bảng dữ liệu cho trước như sau:
Bài tập tìm đơn giá của mặt hàng theo nhiều điều kiện
Ta có công thức như sau: =INDEX(A3:E7,MATCH(H3,A3:A7,0),MATCH(H2,A3:E3,0))
V. Ứng dụng INDEX và MATCH nâng cao trong Excel
Một số thủ thuật excel nâng cao được tích hợp nhằm mục đích nâng cao trải nghiệm người dùng. Ví dụ, với bảng dữ liệu cho trước như bên dưới, hãy điền giá trị vào cột D. Công thức của hàm xác định như sau:
{=INDEX($F$5:$H$16,MATCH(1,(A5=$F$5:$F$16)*(B5=$G$5:$G$16),0),3)}
Trong đó:
- (A5=$F$5:$F$16): so sánh giá trị của A5 với vùng dữ liệu $F$5:$F$16.
- (B5=$G$5:$G$16): so sánh giá trị của B5 với vùng dữ liệu $G$5:$G$16.
Ứng dụng INDEX và MATCH nâng cao trong Excel
VI. Kết luận
Như vậy, với hàm index và match, người dùng có thể dễ dàng tra cứu giá trị dựa theo nhiều điều kiện cho trước. Hai hàm này dùng hiệu quả hơn với hàm tìm kiếm hlookup hay vlookup rất nhiều. Đây là một trong nhiều những thủ thuật excel cung cấp cho người dùng.
Trang chủ 
Mẫu CV 
Nhắn tin 
Tài khoản 
Thêm