Excel Forum

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

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.

Got a Question? Ask it Here in the Forum.