Hello All,



I have been trying to work this out for a while and probably have not given my question the correct title. The formula to lookup a text string and apply to everything in that group also has me stumped.

I have a range of bridge plan numbers in column A.
Some bridges have multiple spans, hence the plan number may appear more than once.
Each span may produce different speed restrictions that the vehicle travelling over it should travel at.
The worst restriction is shown as 'OVERLOADED', followed by '5 kph centre' and finally '10 kph'.

Column D is an example of what I am after.

Is there a formula that will:

1. In a column return the worst case i.e. if one span is OVERLOADED then it will apply OVERLOADED to all other spans of that plan number.
2. In the same column or another column return the worst case i.e. if one span is '5 kph centre' then it will apply '5 kph centre' to all other spans of that plan number.
3. By default if it is '10 kph' all of the spans within the group will also be '10 kph'

So if I was using a nested if formula it would return 'OVERLOADED' then '5 kph centre' and finally '10 kph'.

The MAX or MIN examples in columns I & J are what I am after but it is text, not numerals that I would like returned.

Regards, Dave T

Attached Files

Values per group (forum post).xlsm (18.8 KB, 13 views)