Hàm VLOOKUP rất bổ ích. Tuy nhiên, nó có một số hạn chế, đó là lý do tại sao INDEX MATCH là công cụ thay thế tuyệt vời. Học Excel Online sẽ trình bày về các hạn chế của hàm VLOOKUP và lý do tại sao hàm INDEX MATCH là giải pháp tốt hơn

Công thức cuối cùng 

=INDEX(A3:B8,MATCH(E2,B3:B8,0),1)

Hàm VLOOKUP có ba hạn chế chính có thể dễ dàng loại bỏ bằng cách thay thế hàm VLOOKUP bằng hàm INDEX MATCH

1. Mảng tra cứu phải là dữ liệu ngoài cùng bên trái trong Table Array
2. Mảng tra cứu phải theo thứ tự tăng dần 
3. Yêu cầu tra cứu phải chỉ là một chiều 

Bây giờ chúng ta sẽ xem qua một ví dụ về từng giới hạn trong số ba hạn chế và sau đó giải thích cách áp dụng hàm INDEX MATCH để giải quyết chúng, nhưng trước tiên hãy thiết lập dữ liệu của chúng ta. 

Thiết lập dữ liệu của chúng ta 

Chúng ta sẽ thiết lập dữ liệu của chúng ta để cột A sẽ là Student Ranking (1- 20), Column B:E sẽ là Student Name.  

Lưu ý trong hàng 3 cách chúng ta đã thêm một Class Color. Điều này sẽ được sử dụng sau này để giải quyết tra cứu đa chiều.

Hạn chế của hàm VLOOKUP 1 

Vì dữ liệu hiện đang được thiết lập, chúng ta không bao giờ có thể sử dụng tên trong các column B:E để tra cứu xếp hạng của chúng do thực tế là các hàm VLOOKUP không thể trả về giá trị còn lại của Lookup Array. Bây giờ chúng ta sẽ làm việc thông qua một ví dụ: 

Cú pháp hàm VLOOKUP: VLOOKUP(Lookup Value, Table Array, Return Column, Approximate Match [TRUE/FALSE])

• Trong ô H3, nhập “Sandra” 
• Trong ô H5, nhập công thức này  =VLOOKUP(H3,A4:E23,1,FALSE)

Chúng ta nhận thấy trong Ví dụ này rằng công thức của chúng ta trả về lỗi #N/A như chúng ta mong đợi. Ngay sau đó, chúng ta sẽ khắc phục lỗi bằng cách thay thế hàm VLOOKUP bằng hàm INDEX MATCH, nhưng trước tiên, chúng ta phải đề cập đến sự khác biệt chính với hàm INDEX MATCH. Nó là một siêu hàm với hai hàm khác nhau được lồng vào nhau – hàm INDEX và hàm MATCH 

Giải thích hàm INDEX 

Hàm INDEX có thể trả về một giá trị bằng cách xem xét một table array và sau đó xem hàng và cột được chỉ định của Table Array.  

Cú pháp INDEX:=INDEX(Table Array, Row Position, Column Position) 

• Đầu tiên, xóa tất cả thông tin trong các ô H3:H5 
• Nhập công thức này vào H3 =INDEX(A4:E23,3,3)

Chúng ta nhận thấy trong ví dụ này, công thức trả về “White” vì đó là giá trị nằm ở hàng 3 và cột 3 của Table Array. Tiếp theo chúng ta sẽ trình bày về hàm MATCH 

Giải thích hàm MATCH 

Một mảnh còn lại của hàm INDEX MATCH là Hàm MATCH sẽ trả về vị trí (Hàng hoặc Cột) của nơi tìm thấy giá trị tìm kiếm phù hợp 

Cú pháp MATCH: =MATCH(Lookup Value, Lookup Array, Approximate Match [TRUE/FALSE]) 

• Trong ô G6, thêm một danh mục mới có tiêu đề “Vị trí MATCH” 
• Trong ô H6, nhập công thức này =MATCH(H3,C4:C23,FALSE)

Chúng ta có thể thấy trong Ví dụ này cách công thức trả về “3” vì đó là vị trí hàng của giá trị phù hợp là “White” trong ví dụ này. 

Chèn các hàm INDEX và MATCH 

Khi chúng ta chèn các hàm INDEX và MATCH, hàm INDEX hoạt động giống như trên. Nó xem xét một Table Array được chỉ định cho dữ liệu, ngoại trừ hiện tại, hàm MATCH sẽ chỉ định Vị trí hàng so với khi nó được nhập trong 

Cú pháp MATCH INDEX: =Index(Table Array, MATCH(Lookup Value, Lookup Array, Approximate Match [TRUE/FALSE]) 

• Trong ô H5, nhập công thức này =INDEX(A4:A23,MATCH(H3,C4:C23,FALSE))

Chúng ta có thể thấy trong ví dụ này rằng công thức trả về Student Raking là “3” vì “White” được đặt ở Hàng 3 của Lookup Array. Bây giờ, chúng ta đã biết cách hoạt động của hàm MATCH, chúng ta có thể giải quyết hạn chế VLOOKUP. 

Giải pháp ngoài cùng bên trái hàm INDEX MATCH

Vì hàm INDEX có khả năng chỉ định Hàng và Cột, nên chúng ta có thể cho hàm biết chúng ta muốn nó ở đâu – ngay cả ở bên trái Giá trị tra cứu của chúng ta! Để minh họa, chúng ta sẽ thử cùng một hàm VLOOKUP như trước (tìm Student Ranking bằng cách sử dụng Name) 

• Đầu tiên, xóa tất cả thông tin trong các ô H3:H6 
• Trong ô H3, gõ “Sandra” 
• Trong ô H5, nhập công thức này =INDEX(A4:A23,MATCH(H3,B4:B23,FALSE))

  

Chúng ta thấy trong ví dụ này, công thức trả về Raking là “4” vì đó là vị trí Hàng nơi tìm thấy kết quả khớp với “Sandra”. Tiếp theo, chúng ta sẽ xem xét Hạn chế thứ tự tăng dần của VLOOKUP 

Hạn chế VLOOKUP 2 

Chúng ta sẽ sử dụng cùng một tập dữ liệu, nhưng để minh họa cho điểm này, chúng ta sẽ bỏ sắp xếp Cột Student Ranking. Để làm điều này, chúng ta sẽ sắp xếp dữ liệu theo Cột B. Xem hình bên dưới:

Bây giờ chúng ta đã sắp xếp dữ liệu không theo thứ tự, chúng ta sẽ cố gắng trả về Student được liên kết với Lookup Values (Student Ranking).  

• Đầu tiên, xóa tất cả thông tin trong các ô H3: H6
• Trong ô H5, nhập “3”
• Trong ô H5, nhập công thức này =VLOOKUP(H5,A4:E23,2,FALSE)

  

Như chúng ta có thể thấy từ ví dụ này, công thức trả về lỗi do dữ liệu không được sắp xếp đúng. Một vấn đề tiềm ẩn khác có thể xảy ra là việc tra cứu trả về giá trị sai có thể rất mất thời gian nếu không nắm bắt được. Bây giờ chúng ta sẽ giải quyết vấn đề này bằng cách sử dụng hàm INDEX MATCH. 

• Giải pháp thứ tự tăng dần hàm INDEX MATCH 
• Trong ô H5, nhập công thức này =INDEX(B4:B23,MATCH(H5,A4:A23,FALSE))

  

Trong ví dụ này, chúng ta thấy cách công thức trả về “Clarence” và giải quyết hạn chế thứ tự tăng dần. Bây giờ chúng ta sẽ giải thích lý do chính hàm INDEX MATCH là một giải pháp thay đổi công thức.

Hạn chế VLOOKUP 3 

Công thức VLOOKUP được giới hạn trong một cột tra cứu. Do đó, chúng ta không thể tra cứu cột và hàng để tạo tra cứu tự động. Hãy xem xét một ví dụ mà chúng ta muốn biết tên của học sinh có thứ hạng là “3” trong lớp “Green”. Điều này là không thể sử dụng hàm VLOOKUP.

Giải pháp đa chiều hàm INDEX MATCH 

• Đầu tiên, xóa tất cả thông tin trong ô H3:H6 
• Trong ô H4, nhập “Green” 
• Trong ô H5, nhập” 3 “
• Trong ô H3, nhập công thức này =INDEX(B4:E23,MATCH(H5,A4:A23,FALSE),MATCH(H4,B3:E3,FALSE))

  

Trong ví dụ này, chúng ta có thể thấy rằng công thức trả về “Anna” vì hàng phù hợp cho Student Ranking của “3” là 3 và vị trí cột phù hợp cho “Green” là 3. Do đó, nếu chúng ta tìm Index của Hàng 3, Cột 3 trong bảng dữ liệu, chúng ta sẽ nhận được kết quả là Anna.

Các bạn đã thấy rõ qua các ví dụ rằng hàm INDEX MATCH là một trong những hàm động và hữu ích nhất trong Excel. Học Excel Online mong rằng bài viết có thể giúp ích bạn cho công việc.