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
example.
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.
Range
Name
F2:F12
Code
B1
Make
B2
Model
D2:D12
Range1
E2:E12
Range2
The following array formula displays the corresponding code for an automobile make and model:
=INDEX(Code,MATCH(Make&Model,Range1&Range2,0))
When you enter an array formula, press Ctrl+Shift+Enter (not just Enter).
Note
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 .
Search JabSto ::




Custom Search