Microsoft Office Tutorials and References
In Depth Information
Performing a Two-Column Lookup
Performing a Two-Column Lookup
Some situations might require a lookup based on the values in two columns. Figure 122-1 shows
an example.
Figure 122-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 this table:
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).
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