Excel Forum
Similar Content
Multi-Column ComboBox Drop Down Menus in Forms
Tutorial: Multiple columns of data within a UserForm ComboBox drop-down menu in Excel. I'll show you...
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...



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.