Microsoft Office Tutorials and References
In Depth Information
Using INDEX and MATCH for a Left Lookup
=MONTH(TODAY()) to return a 9 as the second argument of the INDEX func-
tion. (This was written in September, hence the 9).
You ve reached Excel guru status when you start combining INDEX and
MATCH. On its own, neither INDEX nor MATCH seems particularly useful.
Used together, though, they become a powerful combination that is more flex-
ible than VLOOKUP and often faster to calculate than VLOOKUP.
In Figure 12.29 , a customer number is entered in cell A1. The customer lookup
table appears in columns F, G, and H. The main problem is that the customer
table does not have the customer number on the left side.
Figure 12.29.
Figure 12.29. This combination of
This combination of INDEX
enables you to look up
data that is to the left of a key field.
MATCH enables you to look up
data that is to the left of a key field.
In many cases, you would copy column H to column E and use column E as the
key of the table. However, the table in F:H is likely to be repopulated every
day from a web query or an OLAP query. Therefore, it might become monoton-
ous to move the data after every refresh. The solution is to use a combina-
tion of INDEX and MATCH. Here s what you do:
11. Use the formula =MATCH(B1,H2:H89,0) to search through column H to
find the row with the customer number that matches the one in cell B1.
In this case, C499 is in row 9, which is the eighth row of the table.
Search JabSto ::

Custom Search