Excel Magic Trick 478: Reverse Two Way Lookup For Date and Time Column and Row Headers

Online Courses for microsoft excel :

Course updated 2010-01-12 18:21:53

Instructor : ExcelIsFun

Course Duration : 9:34

Rating : 5.00 / 5

Step by step & Descreption :
See how to take a table of schedule information and return all the dates and times for patients with a reverse two way lookup formula that uses the IF SMALL, COLUMNS and OR functions. Then we apply Custom Date / Time Number Formatting.

Amazing Trick from DonkeyOte at the Mr Excel Message Board. This formula improves on the formula as seen in Excel Magic Trick #149.5: Reverse 2-Way Lookup w Duplicates. This trick uses the idea that Dates are Serial Numbers (Integers) and Times are Decimals that present the proportion of a 24 hour day. The COLUMN and ROW functions are added together to help rank and return the correct Date and Time in the correct order.

Reverse Two Way Lookup When Duplicates are Present.