While VLOOKUP is handy for looking up the value in one column and returning a corresponding value from another column, INDEX and MATCH are the popular and most powerful among Excel functions for looking up data.
These functions are invaluable when used on their own, combining them taps into the full potential. Apart from making you look like an Excel whiz to your colleagues and manager, these functions actually save a lot of time. Here’s how these functions work:
VLOOKUP has its limitations. It can only look up values from left to right. The lookup value must be on the left in the lookup table. INDEX and MATCH solves this so you can look up a value anywhere in the lookup table regardless of its position.
Assume you have a spreadsheet with a list of products as in the image. You have columns titled “Product Number”, “Profit”, “Product Name”, and “Revenue”. You have a list of the product names on another spreadsheet. If you want to look up how much profit each product has generated, This is the perfect scenario for INDEX and MATCH.
You’ll be using the product name (our lookup value) to look up the profit. VLOOKUP would not work because the product name sits to the right of the profit.
0 responses on "Top 10 Powerful Excel Functions"