Tutorial: How to do a VLOOKUP with multiple values

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.

Scenario 1

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.

vlookup multiple values

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:

vlookup multiple values

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.

vlookup multiple values

Scenario 2

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. .

vlookup multiple values

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).

vlookup multiple values

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).

 

vlookup multiple values

Scenario 3

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.

vlookup multiple values

(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.

2 thoughts on “Tutorial: How to do a VLOOKUP with multiple values

  1. Roger

    How can I search for student name by student number?
    e.g Student number A0001 to search student name Robert Remus.

    Thanks.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *