# USING MAX WITH INDEX MATCH

#### ANDYMAN

##### New Member
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

Apple 2
Pear 1
Orange 1
Banana 1

Any help much appreciated

Andyman

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi,

Try,

=LOOKUP(2,1/(Sheet1!\$A\$1:\$A\$5=A1),Sheet1!\$B\$1:\$B\$5)

HTH

Excellent! Many thanks for your help.

Andyman

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

Yes I have just found this out!

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

Sorry - Drew also!

Or simply...

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

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

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!

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

Many Thanks all

Replies
2
Views
230
Replies
6
Views
607
Replies
6
Views
365
Replies
16
Views
2K
Replies
0
Views
149

1,212,046
Messages
6,105,590
Members
447,972
Latest member
carrieann

### 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.

### Which adblocker are you using?

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

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