Microsoft Office Tutorials and References
In Depth Information
USE VLOOKUP TO GET THE NTH MATCH
In Figure 56, cell E4 calculates the number of elapsed days with =D4-C4 .
Cell F4 calculates the number of years with =DATEDIF(C4,D4,"y") . Note
that you can combine the formulas from C4, D4, and E4 into a single mega-
formula:
=IF(ISTEXT(B4),DATEVALUE(LEFT(B4,LEN(B4)-4)&RIGHT(B4,4)+1
000),EDATE(B4,12000))-IF(ISTEXT(A4),DATEVALUE(LEFT(A4,LEN
(A4)-4)&RIGHT(A4,4)+1000),EDATE(A4,12000)).
Figure 56. Excel seems to be able to handle dates from before 1900 when you use this
formula.
Gotcha: Historians note that calendar reform in 1752 removed 12 days from
the calendar. Be particularly careful when ﬁ guring dates before this period. The
formula here does not deal with that anomaly. For details, see
Summary: Although Excel doesn’t deal with pre-1900 dates, you can do date
math with these dates by adding enough years to bring them into the post-
1900 era.
This formula was nominated by Barry Houdini
USE VLOOKUP TO GET THE NTH MATCH
Challenge: Your lookup table contains multiple occurrences of each key ﬁ eld.
You would like to return the second, third, or fourth occurrence of the key.
Solution: VLOOKUP cannot solve this problem. OFFSET with MATCH could do
it, provided that the lookup table is sorted by key. But if your table is not sorted,
you need to turn to this user-deﬁ ned function from Zack Barresse and Peter
Moran:

Search JabSto ::

Custom Search