• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VLOOKUP Multiple Values in Multiple Columns

You are here: Home / Excel Formulas / VLOOKUP Multiple Values in Multiple Columns
VLOOKUP Multiple Values in Multiple Columns
May 16, 2013 by Mynda Treacy

A few weeks ago David T asked me to help him understand a VLOOKUP formula in a workbook he’d inherited from a colleague who had left his company.

It was a VLOOKUP formula like nothing I’d ever seen before so I thought I’d share it with you.

Drum roll…..

=VLOOKUP(E2&F2,IF({1,0},First_Name&Last_Name,Grade),2,FALSE)

David’s question was ‘what’s the IF({1,0},… doing’?

First here’s the Excel workbook used in this tutorial if you want to download it.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Ok, before we dive in and try to understand the IF({1,0} we'll start by remembering the syntax for VLOOKUP:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Let’s look at an example (I’ve recreated some dummy data as David didn’t send me his file):

Excel VLOOKUP multiple values in multiple columns

Note: I’ve given the columns A, B and C the following named ranges which are referenced in the formula:

  1. A2:A5 = First_Name
  2. B2:B5 = Last_Name
  3. C2:C5 = Grade

The formula in cell G2 is looking up the names in E2 & F2 and finding matching values in column A (First_Name), & B (Last_Name) and then returning the result in column C:

How Does it Work?

Firstly this is an array formula so it must be entered with CTRL+SHIFT+ENTER.

Excel VLOOKUP multiple values in multiple columns

  1. The formula uses an ampersand (&) to concatenate/join the lookup_values  in E2 & F2 like so:
  2.  
    =VLOOKUP(JimBrown, IF({1,0},First_Name&Last_Name, Grade),2,FALSE)
  3. The IF function creates a matrix, which is the table_array argument for the VLOOKUP formula which consists of two columns.
  4.  

    Remember the syntax for the IF function is:

    IF(logical_test,[value_if_true],[value_if_false])

    The {1,0} matrix are numerical equivalents of TRUE and FALSE and are the logical_test argument for the IF Function.

    Excel VLOOKUP multiple values in multiple columns

    Note how the value_if_true argument also uses the ampersand to join the First_Name’s & Last_Name’s together.

    In the formula it evaluates like this:

    =VLOOKUP("JimBrown",{"JimBrown","A";"BillyJean","A";"SarahJones","B";"BillyRay","C"},2,FALSE)

    Where commas separate columns, and semi-colons separate rows.

    You might find it easier to visualise the table_array like this:

    Excel VLOOKUP multiple values in multiple columns

  5. Lastly the col_index_num simply tells Excel to return the value in the second column of the table_array i.e. the Grade.

Special thanks to Roberto for helping me decipher this formula.

VLOOKUP vs INDEX & MATCH

As I said, I’ve never seen it done this way. I would have used INDEX & MATCH.

Remember VLOOKUP’s sibling is INDEX & MATCH. Some might say INDEX & MATCH is the better looking/more elegant sibling. What VLOOKUP can do, INDEX & MATCH can usually do better.

Here’s how:

=INDEX(Grade,(MATCH(E2&F2,First_Name&Last_Name,0)))

Also entered as an array formula with CTRL+SHIFT+ENTER.

What Do You Think?

Whilst I enjoyed learning this VLOOKUP & IF function trick I still prefer INDEX & MATCH for this type of challenge. It’s not only a shorter and more efficient formula than VLOOKUP & IF, I think it’s also easier to understand.

Have you seen this before? What do you prefer; VLOOKUP & IF or INDEX & MATCH? Let me know in the comments below.

VLOOKUP Multiple Values in Multiple Columns

More Vlookup Posts

vlookup in power query using list functions

VLOOKUP in Power Query Using List Functions

Use List functions to do VLOOKUPS in Power Query as an alternative to merging tables. Sample data and file available to download
Excel VLOOKUP Multiple Sheets

Excel VLOOKUP Multiple Sheets

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

VLOOKUP Multiple Criteria

VLOOKUP Multiple Criteria

Excel VLOOKUP Multiple Values

Excel VLOOKUP Multiple Values

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

Excel VLOOKUP with Dynamic Column Reference

Excel VLOOKUP with Dynamic Column Reference

Excel VLOOKUP with Dynamic Column Reference allows you to copy the formula across columns and have the column reference automatically update.
Excel VLOOKUP to the Left Using CHOOSE

Excel VLOOKUP to the Left Using CHOOSE

VLOOKUP to the left with this clever use of CHOOSE to trick Excel into looking up a column to the left.
hlookup function

Excel HLOOKUP Formulas Explained

How to use Excel HLOOKUP formula, including examples and workbook download.

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: vlookup
Previous Post:Excel BETWEEN FormulaExcel BETWEEN Formula
Next Post:Excel Gantt ChartExcel Gantt Chart

Reader Interactions

Comments

  1. marjorie redondo

    May 13, 2020 at 5:16 am

    Hello! I am trying to use a lookup for my ordering sheet but I am trying to find the logic to formulate it but I haven’t been able. So I create an excel with 3 sheets. One is the Ordering Form. another one the product list with the name, price, half case and bottles, and the order one with my customers. For example, I sell wine by the case which are 12 bottles in the case, but some wines come in cases of 6 bottles only or maybe the customer wants to buy only 2 bottles of wine. So in my productList I have 1 column with the name of the wine, another one with the cost per case of 12 bottles, next column I have the price for 6 bottles, and the last the price per bottle. How can I formulate this in the Order Form sheet? I need when is a 6 pack takes the price of 6 not a full case

    Reply
    • Catalin Bombea

      May 13, 2020 at 3:59 pm

      Hi Marjorie
      You should use an INDEX-MATCH combination, here is a link to a tutorial that will help.
      Your formula should look like this:
      =INDEX(tablerange, MATCH(A1, D5:D20, 0), MATCH(B1, C4:F4, 0))
      where: A1 is the wine name, B1 should be the number of bottles per case. D5:D20 will be that range where you have the wine names list, C4:F4 is the range that contains the numbers of bottles per case headers.

      Reply
      • marjorie

        May 13, 2020 at 10:26 pm

        Thank you so much, Catalin for the information

        Reply
        • Catalin Bombea

          May 14, 2020 at 2:18 am

          You’re welcome!

          Reply
  2. Joseph Ahmed

    October 17, 2019 at 6:21 pm

    Here is something that may be of use to some when using VLOOKUP to add multiple columns of figures (HLOOKUP if rows of course). When adding a couple of columns I was using:
    =SUM(VLOOKUP(A31,HideSheetMains!$A$5:$H$78,4,0),VLOOKUP(A31,HideSheetMains!$A$5:$H$78,8,0))

    I had 8 columns I needed to add within a much larger calculation so I stumbled upon this method; the curly brackets are typed in, not ctrl+shift+enter:
    =SUMPRODUCT(VLOOKUP(A31,’BMS Sub Calcs’!A5:AO78,{34,35,36,37,38,39,40,41},0))
    As you can see the data table had 78 columns with many many calculations needing to be carried out.

    Reply
    • Mynda Treacy

      October 18, 2019 at 11:26 am

      Thanks for sharing, Joseph.

      Reply
  3. Harun

    May 2, 2018 at 5:14 pm

    It’s excellent but I want it from different sheet. Is it possible ?

    Reply
    • Catalin Bombea

      May 2, 2018 at 7:47 pm

      Hi Harun,
      The names you saw in this tutorial (First_Name,Last_Name,Grade) are workbook level defined names, the data can be in any sheet. Simply set the names to our data ranges and use them in formula.
      Catalin

      Reply
  4. Mrutyunjay Patra

    October 17, 2017 at 2:57 am

    I use VLOOKUP for the optional subject means if there is two subject as optional. A student have to choose one and that mark will add to the result . If Roll Number one take 1st optional and roll no-2 take 2nd optional . I also set a rule for blank . If i press roll no 2 the value shown as i want in look up but the formula result give me blank.

    Reply
    • Mynda Treacy

      October 17, 2017 at 7:12 pm

      Hi Mrutyunjay,

      Please post your questions and a sample Excel file on our Excel forum so we can help you. Your question here is difficult to understand without an example.

      Thanks,

      Mynda

      Reply
  5. Mrutyunjay Patra

    October 17, 2017 at 2:51 am

    It is good. But I want to know the formula o v lookup function. I use the V LOOKUP function and result is Blank. It shows a vlue after look up but the formula result show blank.

    Reply
  6. Harold

    December 21, 2015 at 2:44 pm

    Would this have been easier by inserting an extra column and concatenating the first and last name? Then using vlookup or index+match?

    Reply
    • Mynda Treacy

      December 21, 2015 at 5:14 pm

      Hi Harold,

      Sure, you could do that for both tables, but often people want a solution that doesn’t require extra manipulation of the data.

      Mynda

      Reply
  7. manu chand

    January 20, 2015 at 3:49 pm

    i want in my excel sheet remarks collom 0= CLEARE, IF NOT 0 = NOT CLEAR

    LIKE PENDING AMOUNT 500= NOT CLEAR. 0 =M CLEAR HOW?

    Reply
    • Catalin Bombea

      January 20, 2015 at 7:03 pm

      Hi Manu,
      Try a simple IF formula:
      =IF(A1=0,"Clear","Not Clear")
      If you copy this formula down as needed, you will get the “Clear” result only for cells from A column that are 0 or empty.
      Cheers,
      Catalin

      Reply
  8. Diana Kretzschmar

    April 30, 2014 at 6:00 am

    Another Question: What does CTRL+SHIFT+ENTER do? It is said, that it’d be entered with it b/c it’s an array formula?

    Reply
    • Mynda Treacy

      April 30, 2014 at 10:47 am

      Hi Diana,

      An array formula requires entering differently to a regular formula. i.e. with CTRL+SHIFT+ENTER. You can read more about array formulas here.

      Kind regards,

      Mynda.

      Reply
  9. Diana Kretzschmar

    April 30, 2014 at 5:45 am

    Mynda,

    on #3, “Lastly the col_index_num simply tells Excel to return the value in the second column of the table_array i.e. the Grade.” – did you mean the second or third column?

    Diana

    Reply
    • Mynda Treacy

      April 30, 2014 at 10:46 am

      Hi Diana,

      That’s a great question. I meant the second column and this is because we are using the & to join column A and B together so from Excel’s point of view they are the first column and column C is the second column.

      I recomment you use the Evaluate Formula tool to follow the logic and evaluation steps of this formula so you can see how it is working under the hood. Here is a video I recorded on troubleshooting formulas and understanding how they work.

      Kind regards,

      Mynda.

      Reply
  10. jiwan singh

    July 15, 2013 at 3:47 pm

    Dear sir/madam
    when i am puting vlookup farmula in worksheet its not working,is it running with vba code ? if yes plese send us also vba code
    thanks
    jiwan singh

    Reply
    • Mynda Treacy

      July 15, 2013 at 7:48 pm

      Hi Jiwan,

      It doesn’t require VBA but it is an array formula which means you need to enter it by pressing CTRL+SHIFT+ENTER.

      You can read more on array formulas here.

      Kind regards,

      Mynda.

      Reply
  11. Eric Stut

    June 22, 2013 at 5:20 am

    Hi Mynda,

    You wrote: Lastly the col_index_num simply tells Excel to return the value in the second column of the table_array i.e. the Grade.
    The grade is in column C, so isn’t the third column of the table array?
    Thanks in advance for your reaction.
    Kind regards, Eric

    Reply
    • Mynda Treacy

      June 22, 2013 at 2:56 pm

      Hi Eric,

      Good observation, but because we are joining the first and last names together inside our formula, they become the first column and the grade becomes the second column.

      When you look at the formula as it evaluates we can see the comma separates the data into columns and the semicolon onto a new row:

      =VLOOKUP("JimBrown",{"JimBrown","A";"BillyJean","A";"SarahJones","B";"BillyRay","C"},2,FALSE)

      If we were to view the lookup_array above in a tabular format it would look like this with two columns of data:

      JimBrown A
      BillyJean A
      SarahJones B
      BillyRay C

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  12. louay

    May 18, 2013 at 5:21 pm

    Dear Mynda,

    You are Awesome!

    thank you for your share ..

    louay

    Reply
    • Mynda Treacy

      May 18, 2013 at 6:32 pm

      Thank you, Louay 🙂

      Reply
  13. Edwin Kumar

    May 17, 2013 at 2:20 pm

    Great little trick with vlookup. I haven’t done much with arrays on my reports but it looks very powerful way of referencing data. I prefer index & match 🙂

    Reply
    • Mynda Treacy

      May 17, 2013 at 2:52 pm

      Thanks, Edwin 🙂

      Reply
  14. effendi Wijaya

    May 17, 2013 at 10:19 am

    thank you, it’s great post. i never thinking this way before, i prefer use index and match easier than vlookup & if.

    Reply
    • Mynda Treacy

      May 17, 2013 at 10:45 am

      Thanks, Effendi 🙂

      Reply
  15. pmsocho

    May 17, 2013 at 6:05 am

    Awesome. Good to know about that. I have never seen such construction before 🙂
    The similar formula with CHOOSE instead of IF could be:
    {=VLOOKUP(E2&F2,CHOOSE({1,2},First_Name&Last_Name,Grade),2,0)}

    CHOOSE is more fexible than IF because you can create an array with more than 2 columns 🙂

    Recently, I explained one of my students VLOOKUP/CHOOSE and INDEX/MATCH/. She went with INDEX/MATCH because she said it was easier to understand 🙂

    Reply
    • Mynda Treacy

      May 17, 2013 at 10:17 am

      Cheers, Pmsocho 🙂 I like your CHOOSE option too.

      I wrote a tutorial on VLOOKUP and CHOOSE here, but with just a single lookup value.

      Kind regards,

      Mynda.

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x