The team of INDEX & MATCH may make a better combo than using VLOOKUP or HLOOKUP. Here’s an example how….
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Let’s say I have the sales data for 2012 for my crew of 9, and I want to easily see the value for a given salesperson for a specific month, just by entering those into the small table I have. To make it easy on myself and to ensure I don’t mis-type my entries, I’ve utilized Data Validation to select the salesperson and month properly:
The challenges with using VLOOKUP or HLOOKUP are that these both require the data to be in ascending order for an exact match. They also need the row or column where the data is located to be identified. In addition, they can only search from left to right. Not so with the combination of INDEX and MATCH.
In the example above, in cell C17 I want to enter the INDEX function using MATCH functions as the two variables in the INDEX formula. The syntax for the INDEX is:
=INDEX(array,row number,column number). Column number is optional and often excluded. The formula will be =INDEX(C4:N12,MATCH(C15,B4:B12,0),MATCH(C16,C3:N3,0)) and is defined as follows:
– the array is the area where the data is stored. In this case that is C4:N12
– the row is the list of salespeople. It will check cell C15 then look for a match in cells B4:B12. The “0” indicates an exact match. Other options are 1 = less than, and -1 = greater than.
– the column is the list of months. It will check cell C16 then look for a match in cells C3:N3. The “0” indicates an exact match. Other options are 1 = less than, and -1 = greater than.
Now, by selecting the salesperson and month, the formula indexes the values in the table and, based on matches to the criteria in cells C15 & C16, returns the sales value for that salesperson and month:
Not hard at all! Try it yourself with data that you have in a table. Let me know if you come across any issues, but I think you will find this works very well for this and many other scenarios!