I had an email from Bobcat today asking how to lookup data that is spread across multiple columns.

VLOOKUP Multiple ValuesTable 1 has data with the name in just one column:


vlookup multiple criteriaAnd Table 2 has the name across 3 columns (D, E and F):


We want to find the Employee number from Table 1 and put it in column G of Table 2.

The solution is quite simple. We just need to join together the cells containing the name in Table 2, before looking them up in Table 1.

We can do this within a VLOOKUP formula like this:

=VLOOKUP(D6&" "&E6&" "&F6,table_1,2,FALSE)

Note:  table_1 in the above formula is the named range for cells A6:B11

excel lookup multiple criteria

See in the formula bar how I’ve joined the text from columns D, E and F together using the ampersand symbol. I’ve also added a space between the text by inserting double quotes with a space between.

excel lookup multiple values

The VLOOKUP formula is resolving the D6&” “&E6&” “&F6 arguments of the formula like this:

=VLOOKUP(William J Oxley,table_1,2,FALSE)

Thanks for your question Bobcat.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 30 comments… read them below or add one }

Diana Kretzschmar April 25, 2014 at 5:36 am

Great Advice! Thank you!
I have a Question tho, how must the Formula be entered if Table 1′s A-Column was actually 3 Columns, just like in Table 2? Meaning you’d still compare the 3 Columns but Source Table as well as Entry Table are the same Amount of Columns with the 4th (G) being the one that Data is to be populated in.


help me December 4, 2013 at 2:34 pm

20 to 30 column and all column contain some value and all column which contain some value then total of all column and which columns total are lowest then lowest column value in another 31 column shows


Catalin Bombea December 4, 2013 at 11:09 pm

Can you upload a sample workbook, with detailed description of what are you trying to achieve? It will be easier for us to understand the situation, and the solution will come faster!:) You can use the Help Desk: http://www.myonlinetraininghub.com/helpdesk/


kashif Safdar November 27, 2013 at 4:47 pm

nice and thanks


Mynda Treacy November 27, 2013 at 8:08 pm

You’re welcome, Kashif :-)


tim mckenzie June 19, 2013 at 9:00 pm

I have a problem with a vlookup at the moment – how do i contact you for help



Carlo Estopia June 19, 2013 at 9:17 pm

Hi Tim,

Please explain your concerns here: HELP DESK.

It would be best if you can send a mock file.




Bill Fabian May 31, 2013 at 4:58 am

What if we wanted to match three fields of data in on the look up sheet?
If the three fields match 3 fields in the look up sheet true = one of the fields and false = “”


Mynda Treacy May 31, 2013 at 8:08 am

Hi Bill,

I wrote another tutorial on looking up multiple values and matching them to multiple columns here.

In it I show you a way you can use VLOOKUP and the IF function to do this, but at the bottom of the tutorial I show you another (better) way with INDEX and MATCH. Given your question it would go a bit like this:


I hope that helps. If you get stuck please send me your workbook with specific instructions on what you want and where via the help desk.

Kind regards,



caterina June 4, 2013 at 2:34 am

I would like to create a vlookup that would look in a specifica column all the word attached without space and insert one..do you think will be possible?

Many thanks


Mynda Treacy June 4, 2013 at 8:35 pm

Hi Caterina,

VLOOKUP is not the function for the job. You have a few options depending on your data.

Does your data have a character where you want the space e.g. a hypen, comma, full stop etc.?

If so you could just use the Find & Replace tool:

1. Select the cells containing the data you want your spaces added to.
2. CTRL+H to open Find & Replace tool
3. Type in the character you want to replace in the ‘Find’ field
4. Enter a space in the ‘Replace’ field
5. Click Replace or Replace All

If that doesn’t/won’t work please send me your workbook via the help desk so I can see what you’re working with and give you a specific solution.

Kind regards,



adnan ali May 17, 2013 at 12:47 am

linda i want a mltipal name and a diffrent heads i want if i enter head name the other branches of a head is auto,


Mynda Treacy May 17, 2013 at 10:03 am

Hi Adnan,

I’m sorry I don’t understand your question. Can you please send me your Excel file with your data and specific requirements so I can better understand your question.




Abby February 7, 2013 at 5:29 am

Can we do the reverse? I’m importing into a document where the concatenate is placed but I cannot place the concat in the document the data is coming from. In my instance, Doc 1 has site-driver-descri (site, driver, first 5 characters of description) all in col B

The array would be from Doc 2 with no concatenate. Doc 2 has Col A for “site”, Col B for “driver” and i need the first 5 characters of the descrip housed in Col C to match. I need to pull in the value from Doc 2, column J.

A concatenate array seems doable, but I cannot ifgure it out. I attempted to use “Lookup” solution but retrieved no results with that.


Carlo Estopia February 7, 2013 at 6:26 pm

Hi Abby,

I was having a hard time really trying to work around formulas.

So instead of going empty handed on this one, I am giving you a VBA customized function.

Here’s the deal:
1) ALT + F11 ( this will bring you to the VBE Window)
2) In the VBE Window, Go To INSERT menu, Add MODULE (NOTE: NOT CLASS MODULE)
3) In the Module, Paste this code

Function MyVLookup(LookUpValue As String, TBLArray As Range, Col_Return As Integer) As String
    Dim r As Long
    For r = TBLArray.Cells(1, 1).Row To TBLArray.Rows.count
        If TBLArray.Cells(r, 1) & TBLArray.Cells(r, 2) & TBLArray.Cells(r, 3) Like LookUpValue & "*" Then
            MyVLookup = TBLArray(r, Col_Return).Value
            Exit Function
        End If
End Function

4 Here’s how the formula is used- just like any Excel Function


MyVlookup(LookupValue,TBLArray, Col_to_Return)

1 You might encounter security warnings. Just trust it.
2 If VBA is not working, just go to Excel Options, Trust Center, Trust Center Settings, Enable Macro and ActiveX



PS: This is so tailor-made for you that I save ourselves the hassle of creating a very dynamic one. In short,
The Values that this function will look for will be in Cols:A, B, and C only.
Now you may change 1(A), 2 (B)or 3 (C) in the function–shown below in italics– if you want to have another columns to be looked up or you can copy the function in the same module and name it MyVLOOKUP2 etc., and play with the columns you like to be looked up. You may also delete or add more columns by deleting the part “TBLArray.Cells(r,1)” or adding more, respectively.

"If TBLArray.Cells(r, 1) & TBLArray.Cells(r, 2) & TBLArray.Cells(r, 3)"


Kate December 30, 2012 at 3:57 am

Would this work to find the corresponding text value in column “x” when my lookup might be in one of 3 columns

Eg. I have a product available to order from 3 different companies – each company has a different item code. I want to type in the code from any company and find the product name in column “x”



Mynda Treacy December 30, 2012 at 4:18 pm

Hi Kate,

You can use this array formula:


Entered with CTRL+SHIFT+ENTER as this is an array formula.

Where your 3 companies item codes are in cells A2:C6, your product name is in cells D2:D6, row 1 contains your headings and cell F1 contains the product code you want to lookup. If your data starts further down the worksheet than row 2 then you will need to change the -1 in the *ROW($A$2:$C$6)-1 part of the formula accordingly. i.e. I have taken -1 off the ROW formula to account for the header row in row 1, but if your data starts in say row 4 then you would have ROW($A$2:$C$6)-3.

I hope that helps.



Michael December 19, 2012 at 1:11 pm

How about in the opposite situation?

I have around 8,000 cell values displayed in the same format:

21 090016 INSEAL 15mmx1.5mm 90mm LONG

I need the “1″ (acts as an identification point for a position in the photo diagram) to be extracted into a separate cell, the “019784″ (order code) to be extracted into another separate cell and “FILTER BAG FRAME” (product description) to be extracted in another separate cell for all 9,000 cells.

-The identification point or position number can be either 1 or 2 digits but is always delimited by a space only after the numeric value of 1 or 2 digits.
-The order code varies in it’s character count but there is a space before an after the order code value
-The description has spaces throughout the sentence as per above example and there are sometimes numeric values in the description

Can you help me with a formula to split the one cell into 3?


Mynda Treacy December 19, 2012 at 1:58 pm

Hi Michael,

You can use the MID and FIND functions for this:

To extract the order code:

=MID(A1,FIND(" ",A1)+1,SEARCH(" ",A1,4)-SEARCH(" ",A1)-1)

To extract the product description:

=MID(A1,FIND(" ",A1,4)+1,99)

Kind regards,



Tanveer Samana December 1, 2012 at 8:40 pm

I need the excel of the above explanation


Mynda Treacy December 3, 2012 at 8:21 am

Hi Tanveer,

I don’t know what you mean. Can you please explain in a different way?

Kind regards,



Ali Mamedov October 11, 2012 at 9:57 pm

What if Table 1 also contains the First Name, Initial and Last Name in separate columns? There is no way to show anything like that in the array. I have solved that by adding a new column and joining them in the new column but is there a way to do it by another way? Like using if or something else nested?


Mynda Treacy October 11, 2012 at 10:38 pm

Hi Ali,

You can use the LOOKUP function like this:


Where columns G, H and I contain the values you want to lookup and A,B and C contain the table you’re looking up with the result you want returned in column D.

Thanks to Roberto for suggesting that solution.

I hope that helps.



Diana Kretzschmar April 25, 2014 at 6:17 am


I keep getting the #Value! error code on this formula. I had the exact same Question as Ali.
I tried the formula and also variation with ,4) or ,4,FALSE) added to the end of your formula but nothing works.
Here: =LOOKUP(F21&G21&H21,Sheet1!$A$2:$A$350&$B$2:$B$350&$C$2:$C$350&$D$2:$D$350)

Data Jan 2010 – Dec 2010 is the Entry Sheet Column F (City),G (county) & H (State) will be entered and D (Zip) needs to be populated with help of the Index Sheet 1 (Library so to speak).
Index Data is in Sheet 1 Column A-D (City, County, State & Zip)
So IF F&G&H match A&B&C of Sheet 1 then copy Column D data into Data Sheet.

Could you help me please?
Thank you!


Diana Kretzschmar April 25, 2014 at 6:34 am


disregard my previous Question! I got it! :) Here: =LOOKUP(F21&G21&H21,Sheet1!$A$2:$A$350&$B$2:$B$350&$C$2:$C$350&$D$2:$D$350,Sheet1!D2:D350)


Mynda Treacy April 25, 2014 at 9:02 am

Glad you figured it out, Diana :-)


Nicole August 31, 2012 at 3:54 am

What if data is not separated in different columns? Criteria are on the first row and the first column, just like this one:
1 0.1 0.2 0.3 0.4 0.5
2 0.6 0.7 0.8 0.9 1.0
3 1.1 1.2 1.3 1.4 1.5

What is the formula for returning “1.1″ if “A” and “3″ are given?
Thank you!


Mynda Treacy August 31, 2012 at 9:40 am

Hi Nicole,

I’m not understanding your question 100% but if you only want to look up the values in one column then you can use a regular VLOOKUP formula.

If that’s not what you meant, please let me know.

Kind regards,



Kosal Kong August 15, 2012 at 12:38 pm

Im like both V&HLOOKUP, but my usually, commece is VLOOKUP. I am trying to looking for VLOOKUP within multiple criteria. And once again I seeking for advice to to work Piviot excel table


Mynda Treacy August 15, 2012 at 8:37 pm

Hi Kosal,

I’m sorry I don’t understand what you’re having trouble with. Can you please email me with a specific example.




Previous post:

Next post: