Hàm Vlookup không phải là hàm tra cứu duy nhất trong Excel. Thay vào đó, hàm Excel Index và Match linh hoạt hơn và có một số tính năng vượt trội hơn so với hàm Vlookup ở nhiều khía cạnh. Trong bài viết hôm nay EDUSA sẽ hướng dẫn bạn một số cách thay thế hàm Vlookup để thực hiện tra cứu theo chiều dọc trong Excel.
1. Hàm Excel INDEX và MATCH
Bài viết này nhằm mục đích thể hiện hiện rõ hơn chức năng của hàm INDEX & MATCH và sẽ không tập trung nhiều vào cú pháp và cách sử dụng của chúng. Dưới đây sẽ đề cập đến những kiến thức cơ bản và sau đó xem xét các ví dụ chuyên sâu về công thức để thấy được các lợi thế của việc sử dụng INDEX MATCH thay vì VLOOKUP.
1.1 Hàm INDEX – Cú pháp và cách sử dụng
Hàm INDEX trong Excel trả về một giá trị trong một mảng dựa trên số hàng và 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])
Giải thích về từng tham số:
- Array (mảng) – một dải ô mà bạn muốn trả về một giá trị.
- row_num – số hàng trong mảng mà bạn muốn trả về một giá trị. Nếu bỏ qua, thì column_num là bắt buộc.
- column_num – số cột trong mảng mà bạn muốn trả về một giá trị. Nếu bỏ qua, row_num là bắt buộc.
Đây là một ví dụ về công thức INDEX ở dạng đơn giản nhất: =INDEX(A1:C10,2,3)
Công thức tìm kiếm trong các ô từ A1 đến C10 và trả về một giá trị của ô ở hàng thứ 2 và cột thứ 3, tức là ô C2.
Không có gì quá phức tạp. Tuy nhiên, khi làm việc với dữ liệu thực, bạn sẽ khó biết hàng và cột nào bạn muốn, đó là bạn sẽ cần phải sử dụng đến hàm Match.
Xem thêm: Cách sử dụng hàm Excel chia hết cực kỳ đơn giản
1.2 Hàm MATCH – Cú pháp và cách sử dụng
Hàm MATCH trong Excel tìm kiếm giá trị tra cứu trong một dải ô và trả về vị trí tương đối của giá trị đó trong dải ô.
Cú pháp của hàm MATCH như sau:
MATCH (lookup_value, lookup_array, [match_type])
- Lookup_value – số hoặc giá trị văn bản bạn đang tìm kiếm.
- lookup_array – một dải ô đang được tìm kiếm
- Match_type – chỉ định trả về kết quả khớp chính xác hay kết quả khớp gần nhất:
- 1 hoặc bị bỏ qua – tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu. Yêu cầu sắp xếp mảng tra cứu theo thứ tự tăng dần.
- 0 – tìm giá trị đầu tiên chính xác bằng giá trị tra cứu. Trong kết hợp INDEX / MATCH, bạn hầu như luôn cần một kết hợp chính xác, vì vậy bạn đặt đối số thứ ba của hàm MATCH thành 0.
- -1 – tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị lookup_value. Yêu cầu sắp xếp mảng tra cứu theo thứ tự giảm dần.
Ví dụ: Nếu phạm vi B1: B3 chứa các giá trị “New-York”, “Paris”, “London”, công thức dưới đây trả về số 3, vì “London” là mục nhập thứ ba trong phạm vi: =MATCH(“London”,B1:B3,0)
Bạn nên nhớ rằng vị trí tương đối của giá trị tra cứu (tức là số hàng và cột) là những gì bạn cần cung cấp cho các đối số row_num và column_num của hàm INDEX. Excel INDEX có thể tìm giá trị tại điểm nối của một hàng và cột nhất định, nhưng nó không thể xác định chính xác hàng và cột nào bạn muốn.
Xem thêm: Cách dùng công thức Excel hàm ROUND dễ hiểu nhất
2. Cách sử dụng hàm Excel INDEX và MATCH
Nói một cách đơn giản INDEX tìm giá trị tra cứu theo số cột và hàng, và MATCH cung cấp các số đó.
Đối với tra cứu theo chiều dọc, bạn chỉ sử dụng hàm MATCH để xác định số hàng và cung cấp phạm vi cột trực tiếp cho INDEX: =INDEX ( cột để trả về giá trị từ , MATCH ( giá trị tra cứu , cột để tra cứu , 0)
Để hình dung rõ hơn chúng ta sẽ thử nghiên cứu qua một ví dụ. Giả sử bạn có một danh sách các thủ đô quốc gia và dân số của họ.
Để tìm dân số của một thủ đô nhất định, chẳng hạn như thủ đô của Nhật Bản, hãy sử dụng công thức INDEX MATCH sau:
=INDEX(C2:C10, MATCH(“Japan”, A2:A10, 0))
Bây giờ sẽ phân tích công thức:
- Hàm MATCH tìm kiếm giá trị tra cứu “Nhật Bản” trong phạm vi A2: A10 và trả về số 3, vì “Nhật Bản” đứng thứ ba trong mảng tra cứu.
- Số hàng chuyển trực tiếp đến đối số row_num của INDEX hướng dẫn nó trả về giá trị từ hàng đó.
Vì vậy, công thức trên biến thành một INDEX đơn giản (C2: C3) tìm kiếm trong các ô từ C2 đến C10 và kéo đến giá trị từ ô thứ 3 trong phạm vi đó, tức là C4 vì chúng ta bắt đầu đếm từ hàng thứ hai.
Nếu bạn không muốn mã hóa thành phố trong công thức thì có thể nhập nó chẳng hạn F1, cung cấp tham chiếu ô cho MATCH và bạn sẽ nhận được công thức tra cứu động: =INDEX(C2:C10, MATCH(F1,A2:A10,0))
Xem thêm: Cách dùng công thức Excel bỏ ký tự nhanh nhất
Lưu ý quan trọng: Số hàng trong đối số mảng của INDEX phải khớp với số hàng trong đối số lookup_array của MATCH, nếu không công thức sẽ tạo ra kết quả không chính xác.
Tuy nhiên tại sao không sử dụng công thức Vlookup và phải cố gắng tìm ra những điểm phức tạp của Excel Match Index?Ví dụ này chỉ dành cho mục đích giải thích để bạn hiểu cách hàm INDEX và MATCH hoạt động cùng nhau. Các ví dụ khác sau đây sẽ cho bạn thấy sức mạnh thực sự của sự kết hợp này, dễ dàng xử lý với nhiều tình huống phức tạp khi VLOOKUP gặp sự cố.
=VLOOKUP(F1, A2:C10, 3, FALSE)
3. So sánh INDEX & MATCH với VLOOKUP
Khi quyết định sử dụng hàm nào để tra cứu theo chiều dọc, hầu hết các chuyên gia Excel đều cho rằng hàm INDEX & MATCH tốt hơn nhiều so với hàm VLOOKUP. Tuy nhiên, nhiều người vẫn chọn hàm VLOOKUP, thứ nhất là vì nó đơn giản hơn và thứ hai vì họ không hiểu đầy đủ tất cả các lợi ích của việc sử dụng công thức INDEX MATCH trong Excel. Nếu không có sự hiểu biết như vậy, không ai sẵn sàng đầu tư thời gian của họ để học một cú pháp phức tạp hơn.
Nhưng nếu tìm hiểu bạn sẽ thấy MATCH INDEX có những ưu điểm vượt trội so với VLOOKUP .
- Tra cứu từ phải sang trái. Như bất kỳ người dùng thông thạo nào cũng biết, hàm VLOOKUP không tìm kiếm dữ liệu từ bên trái, có nghĩa là giá trị tra cứu của bạn phải luôn nằm ở cột ngoài cùng bên trái của bảng. INDEX MATCH có thể thực hiện tra cứu bên trái một cách dễ dàng.
- Chèn hoặc xóa cột một cách an toàn. Công thức VLOOKUP sẽ bị phá vỡ hoặc đưa ra kết quả không chính xác khi một cột mới bị xóa hoặc được thêm vào bảng tra cứu vì cú pháp của hàm VLOOKUP yêu cầu chỉ định số index của cột bạn muốn lấy dữ liệu. Đương nhiên, khi bạn thêm hoặc xóa cột, số chỉ mục (index) sẽ thay đổi.
- Không có giới hạn cho kích thước của giá trị tra cứu. Khi sử dụng hàm VLOOKUP, các tiêu chí tra cứu của bạn không được vượt quá 255 ký tự, nếu không sẽ xuất hiện #VALUE! lỗi. Vì vậy, nếu tập dữ liệu của bạn chứa các chuỗi dài, INDEX MATCH là giải pháp hiệu quả duy nhất.
- Tốc độ xử lý cao hơn. Nếu các bảng của bạn nhỏ thì sẽ không có bất kỳ sự khác biệt đáng kể nào về hiệu suất Excel. Nhưng nếu trang tính của bạn chứa hàng trăm hoặc hàng nghìn hàng và sẽ phát sinh hàng trăm hoặc hàng nghìn công thức, MATCH INDEX sẽ hoạt động nhanh hơn nhiều so với hàm VLOOKUP vì Excel sẽ chỉ phải xử lý các cột tra cứu và trả về thay vì toàn bộ mảng bảng.
Xem thêm: Cách khóa trang tính Excel không chỉ chỉnh sửa, sao chép
4. Trung tâm đào tạo Excel uy tín – EDUSA
Hiện nay, có rất nhiều trung tâm tin học được mở ra nhằm phục vụ những nhu cầu của các bạn. Tuy nhiên, mỗi trung tâm lại có chất lượng giảng dạy khác nhau, và theo sau đó là những khóa học với chi phí và ưu đãi khác nhau.
Đến với trung tâm EDUSA, bạn sẽ được tư vấn kỹ lưỡng về những khoá học phù hợp với bạn nhất. Bên cạnh đó, những mức phí của khóa học đều nằm ở mức trung bình giúp bạn có thể chi trả một cách dễ dàng. Mặc dù mức phí khá thấp nhưng chất lượng giảng dạy tại EDUSA vô cùng tuyệt vời, đội ngũ giáo viên có chuyên môn cao, nhiệt tình giảng dạy các bạn học viên.
Và đặc biệt hơn, EDUSA sẽ cung cấp một vài buổi học và thi lại miễn phí cho các bạn nếu các bạn thi không đạt chứng chỉ ở lần thi đầu tiên. Thật tuyệt vời phải không nào? Hãy đến trung tâm EDUSA để được tư vấn kỹ hơn nhé.
Xem thêm: Khóa học Excel Offline chất lượng tại EDUSA
Xem thêm: Khóa học Excel Online chất lượng tại EDUSA
5. Câu hỏi thường gặp
5.1 Chứng chỉ tin học có giá trị trong bao lâu?
Chứng chỉ tin học có giá trị vĩnh viễn và không bị hết hạn.
5.2 Tôi có thể đăng ký thi Excel ở đâu?
Bạn có thể đăng ký thi Excel tại các trung tâm đào tạo hoặc các trường đại học, cao đẳng có chương trình đào tạo chứng chỉ này. Hoặc bạn có thể đăng ký tại trung tâm EDUSA để có được chứng chỉ với kết quả tốt nhất nhé.
5.3 Tôi có thể thi lại nếu không đạt được chứng chỉ tin học Excel?
Có, bạn có thể thi lại nếu không đạt được chứng chỉ tin học Excel. Khi bạn học tại EDUSA thi sẽ được cung cấp các buổi học và thi lại “miễn phí” nếu bạn thi không đạt.
6. Lời kết
Đó là cách sử dụng hàm Excel INDEX và MATCH. EDUSA hy vọng các ví dụ về công thức của trên sẽ hữu ích cho bạn. Chúc bạn học tập hiệu quả!
Để lại thông tin cần tư vấn