USING MAX WITH INDEX MATCH

ANDYMAN

New Member
Joined
Mar 17, 2005
Messages
34
Hi - I think these are the functions I need!

I have a list of names in sheet1 column A, some of which appear more than once. In column B I have a version number. I want to create a formula on another sheet that looks for a specific name, matches it on the other sheet and then finds the highest version number for that particular name.

PS the names are not sorted alphabetically so I can't use any Vlookups.

Eg-
Sheet1

Col A Col B
Apple 1
Pear 1
Orange 1
Apple 2
Banana 1

Sheet2

ColA Col B (Latest version number)
Apple 2
Pear 1
Orange 1
Banana 1

Any help much appreciated

Andyman
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Kris's formula will give you the last of multiple records but if your version number isn't sorted then you'll get incorrect results.

I didn't spend much time on this but maybe the following array formula will work for unsorted version numbers....

=MAX((1-ISERROR(1/($A$1:$A$8=D1)))*($B$1:$B$8))

remember to cntrl-shift-enter the formula
 
Upvote 0
Yes I have just found this out!

Thanks Kris this seems to work great regardless of sorting any columns etc.
:biggrin:
 
Upvote 0
Or simply...

=MAX(IF($A$1:$A$8=D1,$B$1:$B$8))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Upvote 0
Thanks both seem to work, however when I posted this thread I ideally wanted an index match so I could then drag the formula across so the next value in Col C could also be displayed.

If I adapt the current formulas, it evaluates each column for the highest value which is not what I need.

Imagine a Col C with a £ value in it, I would need to pick up this value once I've established the latest version.

Hope this makes sense!
 
Upvote 0
Delete the last reply! I've done a concatenation of name & version then indexed matched off this.

Many Thanks all
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top