Sự khác biệt giữa Vlookup và Index Match – Học Excel Online

Dù hàm VLOOKUP cực kỳ hữu ích, một số giới hạn của nó khiến hàm INDEX MATCH trở thành một lựa chọn thay thế hợp lý hơn. Siêu Marketing sẽ phân tích những hạn chế của VLOOKUP và bàn về lợi ích khi sử dụng hàm INDEX MATCH.

Kết quả được đạt được

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

1- Sự so sánh giữa Vlookup và Index Match - Cái nào tốt hơn?
Hình 1 – Kết quả cuối cùng

Ba hạn chế của VLOOKUP có thể được giải quyết nhanh chóng nếu thay thế bằng INDEX MATCH

  1. Phải chọn mảng từ cột ngoài cùng bên trái cho việc tra cứu
  2. Mảng cần được sắp xếp theo thứ tự từ thấp đến cao
  3. Chỉ có thể tra cứu theo một hướng

Bây giờ, chúng ta sẽ nghiên cứu cụ thể từng hạn chế và phương pháp áp dụng INDEX MATCH để giải quyết vấn đề, nhưng trước hết hãy sắp xếp dữ liệu của chúng ta.

Cách sắp xếp dữ liệu

Để bắt đầu, chúng ta sẽ sắp xếp cột A thành Xếp hạng của Sinh viên (1-20), và cột B:E để hiển thị Tên Sinh viên.

Chú ý rằng tại hàng 3, chúng ta đã bổ sung mục Màu sắc của Lớp học. Điều này sẽ giúp chúng ta thực hiện việc tra cứu theo nhiều hướng sau này.

2- So sánh giữa Vlookup và Index Match - Nên dùng cái nào?
Hình 2 – Cách thiết lập dữ liệu

Hạn chế thứ nhất của VLOOKUP 

Do cách dữ liệu của chúng ta được cấu hình, chúng ta không thể sử dụng nó để tìm xếp hạng từ tên sinh viên ở cột B:E vì VLOOKUP không thể tìm và trả về giá trị từ phía trước của mảng tra cứu. Cùng nhìn qua một ví dụ:

Cú pháp của VLOOKUP: VLOOKUP(Giá trị tra cứu, Mảng bảng, Cột trả về, Khớp gần đúng [TRUE/FALSE])

  • Gõ “Sandra” vào ô H3
  • Trong ô H5, nhập công thức sau  =VLOOKUP(H3,A4:E23,1,FALSE)
3- Phân biệt giữa Vlookup và Index Match - Option nào ưu việt?
Hình 3 – Sai lầm rõ ràng khi sử dụng VLOOKUP từ cột bên trái

Kết quả thu được từ ví dụ là lỗi #N/A, điều này là dự đoán trước được. Chúng ta sẽ sửa lỗi này bằng cách thay thế hàm VLOOKUP bằng hàm INDEX MATCH ngay sau. Tuy nhiên, cần phải hiểu rằng INDEX MATCH chính là sự kết hợp giữa hai hàm riêng biệt.

Với việc sử dụng hàm INDEX, ta có thể trích xuất giá trị bằng cách định vị vào một mảng bảng, rồi xác định hàng và cột cụ thể trong mảng đó.  

Cách viết công thức cho hàm INDEX:=INDEX(Mảng Bảng, Vị Trí Hàng, Vị Trí Cột) 

  • Xóa hết các dữ liệu trong phạm vi ô H3 đến H5. 
  • Điền công thức sau vào ô H3: =INDEX(A4:E23,3,3)
4- Hàm Vlookup vs hàm Index Match - Đâu là lựa chọn ưu việt?
Hình 4 – INDEX

Trong ví dụ này, kết quả của công thức cho ta biết “White” chính là giá trị nằm ở giao điểm giữa hàng thứ 3 và cột thứ 3 trong Mảng Bảng. Bây giờ, ta sẽ tìm hiểu về hàm MATCH. 

Giới thiệu về hàm MATCH 

Yếu tố còn lại trong phép kết hợp hàm INDEX MATCHhàm MATCH, hàm này sẽ đưa ra vị trí (dù là Hàng hay Cột) mà tại đó giá trị cần tìm được xác định một cách phù hợp. 

Dạng cú pháp của hàm MATCH là: =MATCH(Giá Trị Cần Tìm, Mảng Tra Cứu, Phương Thức Tra Cứu Gần Đúng [TRUE/FALSE]) 

  • Tạo thêm một cột mới với tiêu đề “Vị trí Match” trong ô G6. 
  • Điền công thức vào ô H6: =MATCH(H3,C4:C23,FALSE)
5- Hàm Vlookup và hàm Index Match - Phương pháp nào tốt hơn?
Hình 5 – MATCH

Qua ví dụ thực hiện, công thức sẽ trả lại kết quả là “3” do đây chính là vị trí hàng mà giá trị “White” được tìm thấy. 

Kết hợp hàm INDEX và MATCH 

Tương tự khi dùng độc lập, hàm INDEX khi kết hợp sẽ tham chiếu đến một Mảng Bảng nhất định để lấy dữ liệu. Tuy nhiên, lần này vị trí Hàng sẽ do hàm MATCH xác định qua giá trị nhập vào. 

Công thức kết hợp MATCH và INDEX: =INDEX(Mảng Bảng, MATCH(Giá Trị Cần Tìm, Mảng Tra Cứu, Phương Thức Tra Cứu Gần Đúng [TRUE/FALSE]) 

  • Điền công thức sau vào ô H5: =INDEX(A4:A23,MATCH(H3,C4:C23,FALSE))
6- Hàm Vlookup và hàm Index Match - Lựa chọn nào ưu việt hơn?
Hình 6 – INDEX MATCH explained

Với ví dụ này, ta thấy rằng giá trị trả về là “3” trong “Student Ranking” vì “White” nằm tại hàng thứ 3 trong Mảng Tra Cứu. Nắm vững cách dùng hàm MATCH, giờ ta có thể giải quyết những giới hạn của VLOOKUP rồi. 

INDEX MATCH và giải pháp cho vấn đề VLOOKUP bên trái cực trái

Khi sử dụng hàm INDEX, ta có khả năng chỉ định được hàng và cột mà không cần giá trị tra cứu phải nằm bên phải. Để minh họa vấn đề này, ta sẽ thực hiện một hàm VLOOKUP như trước nhằm tìm “Student Ranking” bằng cách sử dụng giá trị

  • Bắt đầu bằng việc loại bỏ thông tin trong các ô từ H3 đến H6
  • Ghi chú “Sandra” vào ô H3
  • Nhập vào ô H5 công thức sau =INDEX(A4:A23,MATCH(H3,B4:B23,FALSE))
    7- Hàm Vlookup và hàm Index Match - Hàm nào hiệu quả hơn?
    Hình 7 – Công thức bên trái cùng cho hàm INDEX MATCH

Ở ví dụ trên, công thức mang lại kết quả là “4” vì đó chính là dòng tương ứng với kết quả phù hợp cho “Sandra”. Tiếp theo, chúng ta sẽ phân tích hạn chế của phép sắp xếp theo thứ tự tăng dần trong VLOOKUP

VLOOKUP Hạn chế 2

Sử dụng bộ dữ liệu giống như trước, nhưng để làm nổi bật hạn chế này, chúng ta sẽ không sắp xếp cột Student Ranking nữa. Chúng ta sẽ sắp xếp dữ liệu dựa trên Cột B. Hãy xem hình dưới đây:

8- Hàm Vlookup và hàm Index Match - Hàm nào hiệu quả hơn?
Hình 8 – Dữ liệu được sắp xếp lại không theo thứ tự

Sau khi đã sắp xếp dữ liệu một cách ngẫu nhiên, chúng ta sẽ thử khôi phục sinh viên liên kết với Giá trị Tra cứu (Student Ranking).

  • Xóa hết thông tin trong các ô từ H3 đến H6
  • Nhập vào ô H5 con số “3”
  • Nhập vào ô H5 công thức sau =VLOOKUP(H5,A4:E23,2,FALSE)
    9- Hàm Vlookup và hàm Index Match - Hàm nào hiệu quả hơn?
    Hình 9 – VLOOKUP trong trường hợp dữ liệu không sắp xếp

Quan sát ví dụ này, ta nhận thấy công thức xuất ra lỗi do dữ liệu không được sắp xếp một cách thích hợp. Một khó khăn khác là nếu không nhận ra, việc kiểm tra và sửa lỗi khi tra cứu mang lại kết quả sai có thể mất nhiều thời gian. Giờ đây chúng ta sẽ xử lý vấn đề này bằng cách dùng hàm INDEX MATCH.

  • Cách giải quyết hạn chế của sắp xếp tăng dần bằng hàm INDEX MATCH 
  • Tại ô H5, hãy nhập công thức =INDEX(B4:B23,MATCH(H5,A4:A23,FALSE))
    10- Hàm Vlookup và hàm Index Match - Hàm nào hiệu quả hơn?
    Hình 10 – Hàm INDEX MATCH với cách giải quyết sắp xếp tăng dần

Từ ví dụ, ta có thể thấy công thức cho ra “Clarence” và giải quyết được vấn đề sắp xếp tăng dần. Bây giờ, chúng ta sẽ tìm hiểu lý do hàm INDEX MATCH lại là một giải pháp thay thế công thức hữu hiệu.

Một vài giới hạn của hàm VLOOKUP

Hạn chế chính của hàm VLOOKUP đến từ việc chỉ có thể tra cứu dữ liệu theo cột đơn lẻ, không thể thực hiện tra cứu theo hàng và cột đồng thời. Ví dụ như khi chúng ta cần xác định tên học sinh đứng thứ ba trong lớp “Green”, hàm VLOOKUP sẽ không thể giải quyết vấn đề này.

Sử dụng INDEX MATCH như cách giải quyết theo nhiều chiều

  • Bắt đầu bằng cách xóa hết dữ liệu trong dải ô từ H3 đến H6
  • Nhập “Green” vào ô H4
  • Ghi số “3” vào ô H5
  • Vào ô H3, chúng ta gõ công thức =INDEX(B4:E23,MATCH(H5,A4:A23,FALSE),MATCH(H4,B3:E3,FALSE))
    11- Hàm Vlookup và hàm Index Match - Hàm nào hiệu quả hơn?
    Hình 11 – Phương pháp INDEX MATCH nhiều chiều

Ví dụ trên cho thấy “Anna” là kết quả được trả về bởi công thức này, do đối chiếu hàng số “3” với cột “Green” mang lại trùng khớp cho cả hai, dẫn đến việc tìm ra Anna tại giao điểm Hàng 3, Cột 3 trong bảng.

Với các ví dụ minh họa, chúng ta có thể thấy hàm INDEX MATCH cực kì linh hoạt và bổ ích trong Excel. Siêu Marketing hy vọng bài viết này sẽ hỗ trợ bạn trong công việc hàng ngày.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop