Microsoft Office Tutorials and References
In Depth Information
Using INDEX and MATCH for a Left Lookup
Figure 12.28.
Figure 12.28. INDEX
INDEX can be used in a variety of situations without the
can be used in a variety of situations without the
MATCH
MATCH function.
function.
INDEX becomes interesting when you have a formula calculating the position
argument. Still in Figure 12.28 , a list of people is in M1:M7. You can randomly
select from the list by using INDEX and RANDBETWEEN(1,7), as shown in C4.
If you specify zero as the row or column argument, INDEX returns the entire
row or column. The INDEX in H8 is returning all three values from row 4 of
the table, so you have to wrap the index function in a SUM or COUNT or
AVERAGE function.
The data in row 14 illustrates an undocumented feature of INDEX. When the
reference contains data in a single row, you can specify the column number
as the second argument. To get the data for September, you can use the correct
=INDEX(A14:L14,0,9) or the shortened =INDEX(A14:L14,9). In Figure 12.28 , the
formula in C11 returns the value from the current month by using
Search JabSto ::




Custom Search