Index match max question
I have attached a file for review. Any help is appreciated. I have 3 worksheets. What I am trying to do is find the most current tenant that occupies a property by finding the most recent TEN KEY.
I have tried coming up with a formula to make this work for a couple days, using INDEX, MATCH and MAX- to no avail.
I hope what I'm asking makes sense. Please let me know. Thank you.
Try this array formula entered with CTRL + SHIFT + ENTER
In F3 on the Ten Key sheet
=INDEX('TENANT INFO'!$A$1:$A$10,MAX(IF('TENANT INFO'!$B$1:$B$10=A3,ROW($A$1:$A$10))))
Jonmo1 the formula above works perfectly with the example worksheet, but I can't get it to work on my actual project.
Can I email you my worksheet and let you have a look? I'm trying to acheive the same thing as my example uploaded page?
Why don't you post your actual project, but of course change personal info to bogus made up info.
And only a few rows of data, say 10 or 20.
Okay, you're right. I have uploaded part of my actual project. THANK YOU SO MUCH FOR YOUR HELP AND TIME TO TAKE A LOOK!!
The ranges in your formulas need to begin in Row 1.
Because Index uses a position # relative to the referenced range.
But the ROW function returns the an actual row #,
So the ROW function might be returning say 12
But if you're INDEX begins in Row 11, then the 12th position in that range is actually row 22 (Row 11 is 1, Row 12 is 2, etc)
so H11 should be
=INDEX('TENANT INFO'!$B$1:$B$460,MAX(IF('TENANT INFO'!$C$1:$C$460=B11,ROW($B$1:$B$460))))
Last edited by Jonmo1; 04-03-2013 at 01:15 PM.
Omg!!! You are awesome. Thank you so much!
Great, glad it worked out.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks