ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Microsoft Excel Index Match Lookup

Updated on February 26, 2012

Course Prerequisites

In the last lesson we learned to use Vlookup. Vlookup is very efficient for what it does, but it can become time consuming when thousands of formulas are needed. The case below will illustrate a situation with there is a more efficient alternative.

Let assume this scenario:

You work in the retail banking industry. Your manager has asked you to pull details for a random list of 400 transactions. You are able to export a master list of all transaction details stored on your banks system into an excel file. You must now build a formula to pull the details you need from the table.

I have simplified the transactions needed to 5 for illustration purposes.

The Disadvantage of using Vlookup

If you were to Vlookup instead of the Index Match for the above scenario above it would take roughly three times longer for excel to calculate the formula results. You may think this isn't a big deal but as you begin working with large sets of data it could likely be the difference between 10 and 3 minutes in excels calculation time.

When using Vlookup excel searches a range for a match. In this case for all of the formulas on the same row excel is duplicating the same search. This is very inefficient.


The Advantages of using Index Match

By using the Index function in conjunction with the Match function you can eliminate the duplicate calculations and greatly improve the efficiency with which the formulas are calculated.


The Index Function

The index function is extremely fast when compared to Vlookup. It allows you to reference a cell in a table by defining its column and row number.

=index(Array,RowNumber, ColumnNumber)

Array: This is the range of your table

RowNumber: This is the row number within the table for cell value you want to return

ColumnNumber: This is the column number within the table for cell value you want to return

Let's Look at an Example

The data above represent bushel sales of apples, oranges, and pears. We want to return the number of pears on January 2. Define the following three parameter in your formula to return the data desired.

  • Define our table range B3:D12.
  • Define row number within table 2
  • Define column number within table 3



Using the Match Function

The match function will return the row or column number for a matched criteria in a given range.

=match(lookup_value,lookup_array,match_type)

Lookup Value: This is value you want to match.

Lookup Array: This is the single row or column range you want to return a match in.

Match Type: Use "true" to return an exact match.

Let's consider the same example above. We want to find the row number for 1/2/2012.

We would like to return the number 2 signifying a match is in the second row of our range. We must define the following:

  • Lookup Value: The value we want to match. In this case we must use the date function to convert the date to a value excel understands. I will cover working with dates in Excel at a later date.
  • Lookup Array: The single row or column in which we want to search for a match. In this case A3:A12.
  • Match Type: Use false to return an exact match



Nesting Match in Vlookup

Now that we know how to return the row number of match within our table, we can use this formula within Vlookup to return the value we want to find. Simple replace the row number with the match formula from above.

=INDEX(B3:D12,MATCH(DATE(2012,1,2),A3:A12,FALSE),3)

You should now be able to see how we can perform a task similar to Vlookup by using Index and Match functions.


Back to Our Original Example

Now that we know how to return the row number we need we can complete this task one time and reference it in subsequent formulas.

Begin by inserting a column to right of our lookup data. This column will be use to search for a match in our lookup range

=MATCH(G3,A3:A4596,FALSE)

Note: 4,596 is the last row of my data in my table.

Now reference the row number H3 in our three formulas:

  • =INDEX($B$3:$E$4596,$H3,1)
  • =INDEX($B$3:$E$4596,$H3,3)
  • =INDEX($B$3:$E$4596,$H3,4)

Copy all of the formulas down to the end of your lookup criteria.

The End Result

Congratulations

You now know how to create to lookup values from a table as a speed that will likely far exceed your peers.

Comments

    0 of 8192 characters used
    Post Comment

    • jonhaus profile imageAUTHOR

      jonhaus 

      6 years ago from Kansas City, Mo

      Thanks. Those will problem be covered in my next few. I have plans for many more keep following along.

    • ahmed.b profile image

      ahmed.b 

      6 years ago from Sweden

      Good jonhaus! I appreciate if you would make a hub on usefulness of tagging alongwith sumifs like formulas.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)