Forum Discussion
VLOOKUP multiple values
Hello! I am working on a spreadsheet containing our clients information to upload into a new CRM. The spreadsheet has multiple tabs. Each tab has a separate row for the clients First Name, Last Nam...
Tested the above VLOOKUP formula, it works great! However, after creating tables of the above data and using table references in formula, Excel reports #SPILL! error.
=VLOOKUP(First Name]&[Last Name],CHOOSE({1,2},Table1[First Name]&Table1[Last Name],Table1[Client ID]), 2, FALSE)
Why does Excel report the #SPILL! error when the ranges are essentially the same?
It shall be
=VLOOKUP( [@[First Name] ] & [@[Last Name] ],...
as in OliverScheurich formula, not
=VLOOKUP( [First Name] & [Last Name],...
In first case you combine two values in current row, lookup on it and return some value.
In second case you combine two entire columns, returned result is an array, not single value. It's not enough room for the array in table cell, thus #SPILL! error.