help using combining using Vlookup and MAX/MIN

[quocky28]

Hi,

If you look at the attachment, i think my explanation is really clear in only a few short words. I have a table with multiple names that are the same, and DATA table with the same names but numbers to the right of them. My goal is to get those numbers on the data table, match it up with the name of the table in numerical order.

Confusing to explain but please see my attachment and you'll get what i mean. I tried combining Vlookup and max functions but to no avail. I see people using the index function but i dont understand how it work. Please somebody help if they can!. Thank you

*sorry for the confusing title, English is not my first language!*

[Lemice]

Hello,

You can use this Array formula

Please Login or Register  to view this content.

When you are done editing the formula, remember to Ctrl-Shift then Enter, because it is an Array formula.
If you have trouble, see the attached file, it's your sample with the formula in it.

Hope this help.

[MarvinP]

Hi quocky and welcome to the forum,

Array Formulas are too hard for me, so I like to create helper columns instead and use them. See my answer where I use two helper columns to get what you want.

[quocky28]

Thank you very much! you are a pro!

[quocky28]

Thank you marvin!. array formulas are very hard indeed. i will study both ways and see whats works best for me.

Marvin and Lemice, you two rock!!

[Tony Valko]

First thing...

In your sample file a couple of the entries in Table1 do not have matches in the Data table but this may just be a typo?

The entries in Table1 are listed as CRMBC but in the DATA table they are listed as CRMB.

Enter this array formula** in B2:

=SMALL(IF(E$2:E$7=A2,F$2:F$7),COUNTIF(A$2:A2,A2))

Enter this array formula** in C2:

=INDEX(G$2:G$7,MATCH(B2,IF(E$2:E$7=A2,F$2:F$7),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select B2:C2 and copy down as needed.

You may be able to use a less complicated formula in column C if the combination of the code and the number are unique.

See if this gives you the correct results in column C.

Entered in C2 and copied down:

=SUMIFS(G$2:G$7,E$2:E$7,A2,F$2:F$7,B2)

[quocky28]

that was a typo. thanks for pointed it out. i think your explanation is the best so far Tony, since its "less complicated" im gonna study this some more so i will get better at it. thanks everyone, you all are giving me good answers. much appreciated!

[Tony Valko]

You're welcome. Thanks for the feedback!