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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Drew

Board Regular
Joined
Feb 18, 2002
Messages
185
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

ANDYMAN

New Member
Joined
Mar 17, 2005
Messages
34

ADVERTISEMENT

Yes I have just found this out!

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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,326
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Or simply...

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

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

ANDYMAN

New Member
Joined
Mar 17, 2005
Messages
34
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!
 

ANDYMAN

New Member
Joined
Mar 17, 2005
Messages
34
Delete the last reply! I've done a concatenation of name & version then indexed matched off this.

Many Thanks all
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,073
Messages
5,857,204
Members
431,863
Latest member
SR1SR

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
Top