Forum Discussion
VLOOKUP multiple values
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.
=VLOOKUP([@[First Name]]&[@[Last Name]],CHOOSE({1,2},Tabelle1[First Name]&Tabelle1[Last Name],Tabelle1[Client ID]),2,FALSE)This formula works in my sheet. The name of the left table is Tabelle1 in my sample.
