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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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
19,516

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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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