• 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
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • 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
  • Login

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
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

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

Excel SUMPRODUCT Function

Excel SUMPRODUCT Function

The Excel SUMPRODUCT function is one of Excel's most versatile and underutilized functions allowing for flexible aggregations of data.
Summarize Months to Quarters

Excel Formulas to Summarise Monthly Data into Quarters

3 ways (good, better, best) to summarize monthly data into quarters using formulas. Lots of examples and sample file to download.
Excel BYROW and BYCOL Functions

Excel BYCOL and BYROW Functions

Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows.
python in excel natively

How to Use Python in Excel Natively

How to use Python in Excel natively using libraries like Pandas, NumPy, Matplotlib, Seaborn and more for analysis and spectacular charts!
excel dynamic named ranges

Excel Dynamic Named Ranges

Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas and PivotTables etc.
functions for financial modelling

Excel Functions for Financial Modeling

Top 23 must know Excel functions for Financial Modeling. Includes example Excel file and step by step instructions.
excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
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 Labs (Formerly, Advanced Formula Environment)

Excel Labs is a long awaited, new improved way to write, name and store Excel formulas, including LAMBDAS with the help of AI.


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

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

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.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

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.