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

http://www.adsb.co.uk/date_and_time/calendar_reform_1752/.

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.

Source:
http://www.mrexcel.com/forum/showthread.php?p=1382146

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: