Excel Forum
Similar Content
Remove Spaces Between Text in Excel
Tutorial: How to remove spaces from the middle of text in Excel.  This includes removing all spaces ...
Vlookup on Dates and Times in Excel
Tutorial: Perform a lookup on dates and times in Excel: vlookup, hlookup, index/match, any kind of l...
Convert an Incorrect Date Format into a Real Date in Excel
Tutorial: Change an incorrectly formatted/input date into the proper date format for Excel. This tut...



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.