Công thức mảng bị tràn mà bạn đang cố gắng nhập sẽ vượt quá phạm vi của trang tính. Hãy thử lại với dải ô hoặc mảng nhỏ hơn.
Trong ví dụ sau, di chuyển công thức đến ô F1 sẽ giải quyết được lỗi và công thức sẽ tràn chính xác.
Nguyên nhân phổ biến: Tham chiếu toàn bộ cột
Một hiểu lầm thường gặp xảy ra khi tạo công thức VLOOKUP bằng cách chỉ định quá mức đối số lookup_value . Trước khi có Excel có mảng động , Excel chỉ xem xét giá trị trên cùng một hàng với công thức và bỏ qua bất kỳ hàng nào khác, vì VLOOKUP kỳ vọng chỉ có một giá trị duy nhất. Với sự xuất hiện của mảng động, Excel sẽ xem xét tất cả các giá trị được cung cấp cho lookup_value. Thay đổi này có nghĩa là nếu bạn chỉ định toàn bộ cột làm đối số lookup_value, Excel sẽ cố tra cứu tất cả 1.048.576 giá trị trong cột đó. Sau khi nó kết thúc, nó cố gắng làm tràn chúng vào lưới và rất có thể nó đến cuối lưới, dẫn đến một #SPILL! .
Ví dụ, khi được đặt trong ô E2 như trong ví dụ sau, trước đây công thức =VLOOKUP(A:A,A:C,2,FALSE) chỉ tra cứu ID trong ô A2. Tuy nhiên, trong Excel mảng động, công thức này gây ra lỗi #SPILL! vì Excel tra cứu toàn bộ cột, trả về 1.048.576 kết quả và đến cuối lưới Excel.
Sử dụng một trong các phương pháp sau đây để giải quyết sự cố này:
| # | Phương pháp tiếp cận | Công thức |
|---|---|---|
| 1 | Chỉ tham chiếu các giá trị tra cứu mà bạn quan tâm. Kiểu công thức này trả về mảng động nhưng không hoạt động với các bảng Excel.
|
=VLOOKUP(A2:A7,A:C,2,FALSE) |
| 2 | Chỉ tham chiếu giá trị trên cùng một hàng, rồi sao công thức xuống. Kiểu công thức truyền thống này hoạt động trong bảng nhưng không trả về mảng động.
|
=VLOOKUP(A2,A:C,2,FALSE) |
| 3 | Yêu cầu Excel thực hiện giao cắt ngầm bằng cách sử dụng toán tử @, rồi sao chép công thức xuống. Kiểu công thức này hoạt động trong bảng nhưng không trả về mảng động.
|
=VLOOKUP(@A:A:A:C,2,FALSE) |
Bạn cần thêm trợ giúp?
Bạn luôn có thể hỏi chuyên gia trong Cộng đồng kỹ thuật Excel hoặc nhận hỗ trợ trong Cộng đồng.