Using Excel 2003
This formula works fine for finding the first matching record, but what I need it to do is find the one with the maximum value:
=INDEX('AG2010'!$K$2:$K$1394,MATCH($A3,'AG2010'!$I$2:$I$1394,0))
Last edited by cedarhill; 03-09-2011 at 02:07 PM.
Assuming 'AG2010'!$I$2:$I$1394 contains the numbers...
=INDEX('AG2010'!$K$2:$K$1394,MATCH(MAX('AG2010'!$I$2:$I$1394),'AG2010'!$I$2:$I$1394,0))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I don't see where it takes care of my needing to match the specific equipment ($A3) which is in my original formula...
You are correct, value I am looking in a number in 'AG2010'!$I$2:$I$1394. I am looking for every match of $A3 in another worksheet that contains multiple entries of $A3 and I need the maximum value in 'AG2010'!$I$2:$I$1394. I should have been more specific in my original post. Sorry.
So you need to find multiple matches of A3 in 'AG2010'!$I$2:$I$1394 and at the same time find max in 'AG2010'!$I$2:$I$1394? That doesn't make sense.
is there another column involved here?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Assuming the A3 is to be found in column I and the max is to be found in column J, then try
confirmed with CTRL+SHIFT+ENTER not just ENTER=INDEX('ag2010'!$K$2:$K$1394,MATCH(1,('ag2010'!$I$2:$I$1394=$A3)*('ag2010'!$J$2:$J$1394=MAX(IF('ag2010'!$I$2:$I$1394=$A3,'ag2010'!$J$2:$J$1394))),0))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I've tried to attach a copy of the sample spreadsheet, but I keep getting an error. It's only 752K and it has the right file extension (xls). Don't know what the problem is.
However, I'll see if I can explain better. I have a worksheet AG2010 that has a year's worth of individual meter readings for equipment. Each reading shows the Unit # and the Reading. On a separate Summary work, sheet, I am summarizing data for each unit. So I need to find all the entries for a Unit on the years worth of entries and need to post only the largest meter reading found.
The question is, in what column are are the Units entered in AG2010 and in what column are the meter readings found?
Did you see my last formula above? Can you adapt it?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I missed your last message while I was trying to upload the file. Will check it out. Thanks.
It just dawned on me, that maybe you want just:
=MAX(IF('AG2010'!$I$2:$I$1394=$A3,'AG2010'!$K$2:$K$1394))
confirmed with CTRL+SHIFT+ENTER where column K contains the meter readings?
Is that it?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
It's displaying the max meter reading in the meter column, which is for another unit, not the Unit I've selected.
I've again tried to upload the file...still getting an error and the message to contact support.
IT WORKS!!!! Your formula was absolutely correct!! I was in error. Thanks for your patience.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks