Excel Tutorial Series (Part 2): INDEX, MATCH, and Error Handling

January 31, 2012 8 Comments by Tom Fang

If you haven’t watched Part 1 of the Excel tutorial series, please go and watch that first.  Remember to download the Follow Along Workbook to follow along with the video. If you’ve already completed Part 1, use the same file that you worked on for this part.

Excel Tutorial Series

In this second part, we’re going to go over one of the most powerful functions in Excel: INDEX, and the function MATCH that make it great.  With these two functions, Excel is able to filter and find the exact values you need from a large table.  Don’t miss this one!

As usual, please leave any questions you may have in the comments section.  Enjoy.

Mastered Part 2? Go to Part 3 now!

  • Pingback: Excel Tutorial Series: Part 1 - Manipulating Tracking Codes | iPyxel Creations | Straight-to-the-point internet marketing services and software

  • Pingback: Excel Tutorial Series (Part 3): Filtering Unique Entries and Combining Reports | iPyxel Creations | Straight-to-the-point internet marketing services and software

  • nz_mike

    Hi, really good videos.  However, if it was me I would have just used a VLOOKUP formula instead of the INDEX & MATCH formula as I think its easier to use, especially for beginners.  Granted VLOOKUP’s can’t always be used if the lookup item is on the right side but in this case it works fine. But still top notch videos.
    CheersMike

  • http://www.ipyxel.com/ Tom Fang

    Hey Mike, thanks for the input!

    Yes, you’re absolutely right VLOOKUP would have worked just the same in this instance.  My thought was INDEX/MATCH can handle any look-ups in any direction as well as looking up values in a 2-dimensional array.  I thought it would be easier for people to remember 1 formula instead of teaching when to use what, but I probably should have mentioned that VLOOKUP and HLOOKUP can perform similar functions for one-way look-ups.

    There is a downside to VLOOKUP/HLOOKUP that prevent me from ever using them though.  For example, if I wanted to use values in column A to look up values in column G, I’d have to select everything in between as part of the look-up range.  This becomes a problem when I want to trace dependents on values between columns B and F.  Excel thinks that the look-up formula depends on all those cells in that range.  So when you need to trace dependents, it becomes very difficult to audit.

    -Tom

    • nz_mike

      Hi Tom

      I don’t quite follow the problem with auditing the vlookup, but it just could be because it is 2.50am here and I need to get to bed, lol.  If you named the range would it be better for auditing?

      I quickly went thru your 7 day POF course the other night and its excellent.  I have to re read it to take it all in so might come back to your for some advice in a couple of days if that’s ok.  Will be in touch.

      Cheers
      Mike

    • http://www.ipyxel.com/ Tom Fang

      Of course.  Glad you find it useful.

  • Joe

    Hi Tom, Thanks so much for posting the tutorials. You saved me a great deal of time in figuring out a lot of things. I was ecstatic when I stumbled upon your website!

    Noob question here: on the excel sheet tab showing prosper’s data, how do you get it to show the # of leads? did you have to add the global pixel or post back url to the cpa network? or will using only subids work?

    • http://www.ipyxel.com/ Tom Fang

      Joe, thank you for being ecstatic!

      Yes, you’re right about having to give the CPA network your pixel or post back URL. You can also use Prosper202′s manual subid updating thing, but it’s not as good. Just work with your network to get your pixel firing correctly.