Sometimes the value that you’re searching for isn’t neatly in one column. An example would be where you’re looking in the data for a first name + last name combination but your lookup table has them in separate columns (or vice versa – we’ll go through both scenarios).
You can follow along with the VLOOKUP multiple values example excel file to make the following scenarios more real to you.
In Scenario 1 we want to return a value from the WhereToSearch table on the right into the SearchFor table on the left but the SearchFor table has the first name and last name in separate columns.
What we effectively need to do in the VLOOKUP formula is join these names together (with a space between them) and then do the VLOOKUP as normal.
Joining the values in cells B4 and C4 together (with the space in between) in an excel formula would be
= B4 & ” ” & C4
So we can build this into our VLOOKUP formula in the SearchFor part of the formula to be as follows:
You can then drag the formula down column D (in this case for Meggan Mohan) and tada! You have the student numbers automatically filled in.
In Scenario 2 we want to return a value from the WhereToSearch table on the right into the SearchFor table on the left but the WhereToSearch table inconveniently has the first name and last name in separate columns. .
The VLOOKUP formula on its own can’t do this but a fix is to insert a column after column F and do the same thing as in Scenario 1 above and join the First Name and Surnames together (with a space in between).
We can then drag this formula down to populate the new column. The VLOOKUP in the SearchFor table can then be entered as normal with no joining formulas required within it (see formula below).
A final scenario could be where both tables have the name columns separate in which case you can either replicate the solution in Scenario 2 (insert a column joining the two name values) in both your WhereToSearch and SearchFor tables.
(Hot tip: If your tables are unlikely to change then it’s worth copying and pasting values for the new columns)
Want to get good at using the VLOOKUP function?
Download our practical VLOOKUP tutorial workbook with step-by-step instructions and examples to help you become a VLOOKUP pro.