If contains "text string" then return highest value from key (advanced)

[Batvatar]

Hello all,

I have an advanced "if then" question, thank you in advanced for any help..

I have 45,000 accounts, some duplicates that had 1 piece of equipment per account... I already concatenated the information to give me 20,000 UNIQUE accounts with a cell of multiple pieces of equipment separated by commas... on Tab 2 I have a list of different pieces of equipment that have a value assigned to each piece of equipment...

What I need is a formula that will do the following... look for a piece of equipment that is on tab 2 in the concatenated information, and bring back the value of THE HIGHEST piece of equipment that is labeled on tab 2.. The trick here is that the concatenated information will have multiple matches to the equipment list on tab 2... but the values are all different.. so I need it to look at the table, and find the equipment with the HIGHEST value from tab that is matched int the concatenated info.. then I need to be able to copy that down about 20,000 times...

thanks again everyone!

Example:

Tab 2: the Key

[TABLE="width: 206"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]Account 1
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]Account 2
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Account 3
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Account 4
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Account 5
[/TD]
[TD]8
[/TD]
[/TR]
</tbody>[/TABLE]



Tab 1I'm trying to solve for "C")

[TABLE="width: 457"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Account 1
[/TD]
[TD]machine 1, machine 2, machine 3
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Account 2
[/TD]
[TD]machine 1, machine 2, machine 5
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Account 3
[/TD]
[TD]Machine 1, machine 3
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Account 4
[/TD]
[TD]machine 3, machine 5
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Account 5
[/TD]
[TD]machine 5
[/TD]
[TD]8
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Batvatar]

probably another typo on my part, but I think you nailed the idea, i'll try to input this formula you put down and see what comes of it and report back!