Microsoft Office Tutorials and References
In Depth Information
Tip 36: Performing a Two-Column Lookup
Performing a Two-Column Lookup
Some situations may require a lookup based on the values in two columns. Figure 36-1 shows an
Figure 36-1: This workbook performs a lookup by using information in two columns (D and E).
The lookup table contains automobile makes and models and a corresponding code for each one.
The technique described here allows you to look up the value based on the car’s make and model.
The worksheet uses named ranges, as shown in the following minitable.
The following array formula displays the corresponding code for an automobile make and model:
When you enter an array formula, press Ctrl+Shift+Enter (not just Enter).
This formula works by concatenating the contents of Make and Model and then searching for this
text in an array consisting of the corresponding concatenated text in Range1 and Range2 .