Finding the maximum matching corresponding value?

[bobbly_bob]

Ok guys, this should be easy, I've been trying to get it to work with
vlookup along with Max but I'm just either not getting the right
answer, or as soon as one of the conditions changes I get an error. I
need excel to be able to do this every time without an error.

Basically there are 5 of us using the one sheet. We all have a list of
tracking numbers. The tracking number is individual to the user, they
ascend by one every time a new entry is placed in the list. With
individual sheets this was simple enough for us to do, but we're trying
to get it down to just one shared sheet. As an example

AB 101
AB 102
DC 201
AB 103
EF 301
EF 302
DG 401
AB 104
DC 202

So if my next entry were AB, i need it to match the previous highest
value allocated to AB and add one, so it would be given the number 105,
if it were DC entered then 203 etc.

 

[Biff]

If I understand what you want this will work if there is already a list
started.

Formula entered as an array using the key combination of CTRL,SHIFT,ENTER
not just ENTER:

=MAX(IF(A1:A9="AB",B1:B9))+1

If you're starting a list from scratch I think you'll need an event macro
(if I understand what you want). In fact, the more I think about it the more
I think a macro is what you need. I can't help with that.

Biff

 

[Leo Heuser]

Ok guys, this should be easy, I've been trying to get it to work with
vlookup along with Max but I'm just either not getting the right
answer, or as soon as one of the conditions changes I get an error. I
need excel to be able to do this every time without an error.

Basically there are 5 of us using the one sheet. We all have a list of
tracking numbers. The tracking number is individual to the user, they
ascend by one every time a new entry is placed in the list. With
individual sheets this was simple enough for us to do, but we're trying
to get it down to just one shared sheet. As an example

AB 101
AB 102
DC 201
AB 103
EF 301
EF 302
DG 401
AB 104
DC 202

So if my next entry were AB, i need it to match the previous highest
value allocated to AB and add one, so it would be given the number 105,
if it were DC entered then 203 etc.

Bobby

Maybe this is, what you're after:
Assuming a 2-column list (named ValueBlock), where AB, DC, EF and DG get
their initial values.
Further assuming entries start in A2 (A1 must not contain data found
in A2 and down, and the list can't start in row 1)

In B2 enter this array formula:

=IF(COUNTIF($A$1:A1,A2)=0,VLOOKUP(A2,ValueBlock,2,0),OFFSET($B$2,MAX(IF($A$1:A1=A2,ROW($A$1:A1)-ROW($A$2))),0))+1

to be entered with <Shift><Ctrl><Enter>, also if edited later.

Copy B2 down as far as necessary.