Lunchtime learning - Excel
to lunchtime menu
Do make sure that any tips and suggestions work as you expect them to in your own particular circumstances.
Two dimensional lookups
VLOOKUP() and HLOOKUP()
If you need to look up items in a one dimensional list then VLOOKUP() or HLOOKUP() are the obvious functions to use. VLOOKUP() will look vertically through a list to find a match and then return a value from a designated column on the matching row. HLOOKUP() does the horizontal equivalent. Here we have an example using VLOOKUP() and HLOOKUP(). The final, optional, parameter 'FALSE' sets the type of lookup to perform to 'Exact'.
In both cases, only one of the dimensions is an actual 'lookup', the other is just a manually entered column or row position.
MATCH() and INDEX()
If we needed to lookup up both dimensions then there are several alternatives. Perhaps the most straightforward is to use the Lookup Wizard Excel add-In. This takes you step by step through the process of selecting a cell at the intersection of a matched row and matched column. The resulting formula uses MATCH() and INDEX(). Note that it has only found the first MATCH() of 102 and ignores the second one:
For a fuller explanation of the Lookup Wizard and INDEX() and MATCH() have a look at:
Array formula and SUMPRODUCT()
Now for some alternatives. Both are based on Boolean logic – the application of the maths of TRUE and FALSE. In effect, TRUE=1 and FALSE=0, so we can multiply a value by a number of statements that evaluate to TRUE or FALSE and if they are all true the value will be multiplied by one or more ones but if one or more are FALSE the zero or zeros in the multiplication sum will ensure the overall result is 0.
Method 1 uses an array formula to multiply our grid of values by statements that our row value equals the value we want to match and that our column value matches. Array formulae can be very useful but must be saved when entered and edited using CONTROL+SHIFT+RETURN – this surrounds the formula in curly brackets and makes it an array formula. Many users will not have come across array formulae and will therefore find it hard to understand what is going on.
To address these issues method 2 uses the SUMPRODUCT() function which works in a very similar way to an array, and multiplies all its arguments together:
Note that the array formula and SUMPRODUCT() both return the total of all the matches, not just the first match. Their other difference is how a failure to match one or both of the values is treated. The INDEX(), MATCH() combination will generate a #N/A error, while the array and SUMPRODUCT() formulae will just return zero:
Checks and controls
There's a lot that can go wrong constructing complex lookup formulae, so always make sure to test the results and build in appropriate checks and controls.
|© 1998 The Knowledge Base. Disclaimer Contact firstname.lastname@example.org
Design by Reading Room Ltd 1998