Finding Max in Lookup Table with Multiple Matches

Hi All,

I need a little help. Attached is an image showing my problem. Essentially I have a lookup table where the value I am looking up is in multiple rows. I'd like to find the "Max" Lease end Date for a given Tenant ID. Any help? I'm thinking VLOOKUP or Index/Match, but I can't seem to find the right combination. Any help is much appreciated!

PLease see post #5 for the excel attachment, sorry all, as I am new to the forum!

TableA.png

Tenant ID Name Step Rent Start End
1 AA 1 $24.50 9/1/2012 6/30/2014
1 AA 2 $27.00 7/1/2014 7/31/2015
1 AA 3 $27.25 8/1/2015 8/31/2016
1 AA 4 $27.50 9/1/2016 8/31/2017

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

exactly the same place that you uploaded the picture

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

OK thx, see if this solves that sucker!!

3 variations for you...
1. =MAX(IF(B2:B7="AA",F2:F7,0)) hard coded AA - needs to be changed manually each time
2. =MAX(IF(B2:B7=G1,F2:F7,0)) I entered AA into G1, any entry you make in G1 will be evaluated
3. =MAX(IF(B2:B7=B2,F2:F7,0)) I used the value IN the table to determine what to check for

take your pick (I prefer 2 - or 3 with a table of names)