fbpx
Search
Close this search box.

VLOOKUP that fat table with ease [3 quick tips]

Share

Facebook
Twitter
LinkedIn

Time for some good, old fashioned VLOOKUP love. Let’s say you are writing VLOOKUP()s to get data from an unusually fat table, ie one with heaps of columns. You want to get to lookup ID in first column and get thingamajig in what is that column number. Well, better get counting from 1 and after 19 seconds and lots of squinting you arrive at column number 53 – which has thingamajig. 

If this sounds like your VLOOKUP routine, check out these three amazingly simple tips to save some time and effort with your lookups.

#1 Switch to R1C1 view

This is a quick and easy fix. Head to File > Options > Formulas and enable R1C1 reference style. This will tell you what each column is in number format. Problem solved.

use-r1c1-view-to-get-column-numbers-vlookup-v1

Of course, this also means all your formula references will be turned in to R1C1 style. But once you disable the R1C1 reference style in Formula Options, your VLOOKUP will be back to A1 style.

#2 Use Tables

While the R1C1 view can quickly tell you what each column is in numbers, it won’t work if your data starts from 17th column or something like that. A better option is to turn your raw data in to tabular format using Insert > Table. Give this table a name from Design ribbon, like mydata. This way, you can use simpler lookup formulas.

[Related resources: VLOOKUP with Tables is awesome | Introduction to Excel Structural Referencing for table formulas]

Original VLOOKUP: =VLOOKUP(something, data!$A$2:$BK$123, 53,false)

VLOOKUP using tables: =VLOOKUP(something, mydata, 53,false)

But we still have to figure out the column for thingamajig. Simple, we use MATCH() formula inside VLOOKUP, like this:

VLOOKUP using tables & MATCH(): =VLOOKUP(something, mydata, MATCH("thingamajig", mydata[#Headers], 0), false)

That is right, you can access table headers using the #Headers keyword and get position of any header.

#3 Use INDEX + MATCH

This will make the problem altogether irrelevant. Simply use INDEX and MATCH formulas to get the result you need, like this:

=INDEX(mydata[thingamajig], MATCH($C$3,mydata[ID],0))

Now, you don’t care if thingamajig is 53rd column or 217th column and if ID is at the start or somewhere else. It works all the same.

For more about using INDEX in your formulas, check out this beautiful tutorial.

Here is a summary of all formula techniques

Normal Lookup [help]
Formula =VLOOKUP(something,'really fat data'!$A$2:$BK$123,53,FALSE)
Comments You need to know which column (53) has the data you need. Either count manually or enable R1C1 style to quickly to know.
Using Tables [help]
Formula =VLOOKUP(something,mydata,53,FALSE)
Comments Create a table for your source data (using CTRL+T) and give it a name. Use the name in your formulas. You still need to know column number.
Using Tables + MATCH() 
Formula =VLOOKUP(something,mydata, MATCH("thingamajig",mydata[#Headers],0),FALSE)
Comments Use MATCH() to find out which column header has the value you want to lookup.
Using INDEX + MATCH [help]
Formula =INDEX(mydata[thingamajig], MATCH($C$3,mydata[ID],0))
Comments Now, you don’t even need to know where everything is. Just lookup the value in ID column and get corresponding value from thingamajig column. Done 🙂

Download Example Workbook

Click here to download workbook with all vlookup techniques discussed in this page. Play with formulas to learn more.

Get your VLOOKUP on…

Check out our complete guide to VLOOKUP and for more lookup pizzazz get my VLOOKUP book.

How do you deal with fat tables?

I use tables and INDEX+MATCH whenever my data is more than a few columns or can change often.

What about you? How do you deal with several columns situation? Please post your tips in the comments section.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

13 Responses to “VLOOKUP that fat table with ease [3 quick tips]”

  1. Bob says:

    If you want to know a column number you can just use COLUMN() and pick literally any cell in the column you want to refer too (with an absolute reference if you want to drag the formula)...

    • Alexander says:

      Using COLUMN() limits you to tables starting in column A.
      I have used COLUMNS() instead selecting the rows up to and including the required column.
      This is particularly good, if you are referencing multiple columns and effectively extracting a record:
      e.g. VLOOKUP($A$1,$D$10:$G$20,COLUMNS($D$10:E$10),0), which produces the matching value from the second column, when copied right will produce VLOOKUP($A$1,$D$10:$G$20,COLUMNS($D$10:F$10),0), which produces the matching value from the third column.

  2. Xiq says:

    A long time ago in a Excel-sheet far, far away.... when you could still find vlookups in my workbook....

    The only reason why leave a vlookup formula in a workbook, is because the owner of the document might not understand the match & index combo 🙂

  3. GDRIII says:

    Why VLOOKUP when you can PowerPivot?

    • Chandoo says:

      @GDRIII: Interesting question. Unfortunately a majority of Excel users are still not familiar / have / use Power Pivot for most of their work. So VLOOKUP it is.

    • GraH says:

      When you need a direct response... meaning, without clicking refresh, a vlookup (or alternative formula) is the way to go. But else, PQ anytime.

      • Chihiro says:

        I love PQ, but I often find a lot my colleagues are in one of two sides on it.

        1. Know Excel, but no clue about PQ. Don't know it even exists. Are afraid to even touch it.
        2. Know TSQL (or other variants) and thumbs their nose at PQ. Thinks Excel is for accountants and not meant for tech savvy.

        I know of only 1 other person in the company that uses PQ/PowerBI.

        Me, I think each has it's own use and having more tool to pick from when working on a solution is a good thing. Trying to convince more people to pickup PQ.

  4. Richard says:

    As a quicker option to point 1, and especially when you are not starting your table in column A, when you are selecting your reference range, Excel (I'm using 2013) shows a pop-up showing the dimensions of your table e.g.: (50R x 20C); so as you scroll right selecting your table range you can note what the column number(s) of your desired columns are.

    • Vipul Karkar says:

      That's what I do all the time.
      The tooltip near the cursor while selecting the lookup range gives a clear idea how many columns and rows are selected.
      And it works in all versions of excel.

      Voila.!!

    • Chris H says:

      Was just about to post the same thing! Great quick way of identifying the column number.

  5. Stephen says:

    Brilliant!

    I created a macro so I can toggle this from my quick access toolbar:

    Sub ToggleReferenceStyle()
    If Application.ReferenceStyle = xlA1 Then
    Application.ReferenceStyle = xlR1C1
    Else
    Application.ReferenceStyle = xlA1
    End If
    End Sub

  6. Agnelo Pereira says:

    Sir;
    I have all headers in left most column and in rows, sometimes the headers have duplicate entries, let's say there is header "Minority Interest" in Column one and Row fifteen, then again "Minority Interest" appears in Column one and Row Thirty Four.
    Numerical data are in subsequent Columns for each header. I am using
    VLOOKUP, and this function takes numerical data only for the Column one Row Fifteen "Minority Interest", and it ignores the Column one Row Thirty Four "Minority Interest" numerical data.
    Can You guide me how to resolve this, data downloaded is of Financial Statements of companies, and Balance sheet or Profit loss account statements have additional headings at times.
    Regards
    Agnelo

Leave a Reply