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 Name & Client ID. There is one master tab containing all of the information but the other tabs only have the clients First and Last name. I need to match each Client ID from the master tab to the correct names on the other tabs.
I am hoping that VLOOKUP (or some excel function) can help me with this. Thank you so much for your help!!
5 Replies
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.
=VLOOKUP(F5&G5,CHOOSE({1,2},$A$5:$A$21&$B$5:$B$21,$C$5:$C$21),2,0)You can try this VLOOKUP formula.
=INDEX($C$5:$C$21,MATCH(1,($A$5:$A$21=F5)*($B$5:$B$21=G5),0))An alternative could be INDEX and MATCH.
The formulas have to be entered with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
