INDEX MATCH MATCH and return MAX value of multiple matches

I have a list of times restaurant checks were printed on any given day. There is a front page that uses the data of business and check number as the MATCHES while the time is the INDEX. Since a check may be printed multiple times I need to get the last time the check was printed.


DATA




DATE OF B | CHECK # | PRINT TIME

4/16/18 50014 12:41:00
4/16/18 50014 12:42:00
4/16/18 50014 12:53:00

Try something like this:

D2 =LOOKUP(2,1/(B$2:B$100=B2),C$2:C$100)

Drag the formula down column D.

See attached. The formula below should do the trick

=LOOKUP(1,0/FREQUENCY(0,1/(1+($A$2:$A$6=$B$9)*($B$2:$B$6=$B$10)*$C$2:$C$6)),$C$2:$C$4)

I am working from a data story sheet which pulls from several other sheets and the print data is one of those other sheets. The column of print times is 14K long so I cannot set parameters other than the entire column.


How can I revise this formula

Originally Posted by njm0059

The column of print times is 14K long so I cannot set parameters other than the entire column.

Your version of Excel has 1,048,576 rows. If your data is 14,000 rows long, that's less than 2% of the total rows.
Why do you say that you have to refer to the entire column when you are only using < 2% of it?

Simply changing the formula in post #1 to =LOOKUP(2,1/(B$2:B$15000=B2),C$2:C$15000) should work fine. Using whole column references here will grind the formula to a halt.

If you are constantly adding and removing rows, I would convert your data into a table and use table ranges.

This would include the entire table column without going any further than is necessary.

I have converted into a table but I was referencing PM110s example he sent where he restricted the formula to just the date range i needed