Assign text string against groups of data

p45cal · #10 04-09-2021, 06:05 AM

Try:

Code:

=INDEX({"10 kph","10 kph","10 kph","5 kph","5 kph","5 kph","OVERLOADED","OVERLOADED","zzz"},MAX(IF($A$3:$A$153=A3,IFERROR(MATCH($B$3:$B$153,{"OK [Ru]","OK [Vu]","10 kph","RESTRICTION","5 kph centre","Analysed","Overloaded [Vu]","OVERLOADED"},0),9))))

Note that:
1. There's a different number of possibilities inside each {}; 8 in one 9 in the other.
2. The last one of the 9, currently "zzz" is what appears if ANY of the spans for a given bridge is unrecognised or blank. Adjust this for what you want. If you want it to be blank leave it as "" (with NO space between the double-quotes).
3. The IFERROR part returns 9, the last value. If you want to add more values you'll need to alter this.
4. I haven't tried to 'be clever' with the formula.
5. If you want blanks in column B to be treated differently, say so.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Search for specific data in a text string	teligence	Excel	1	05-20-2017 11:51 AM
How to get groups of data on each page	aldumil	Mail Merge	7	06-24-2015 01:28 PM
Sorting Groups of Data, keeping continuity	carter	Excel	1	04-06-2015 01:25 AM
Way to search for a string in text file, pull out everything until another string?	omahadivision	Excel Programming	12	11-23-2013 12:10 PM
Assign resource groups to tasks	jjmclell	Project	2	12-17-2012 04:28 AM