Complex Comparison forumula - Help

**Rjwilliams93** · 07-08-2013, 12:24 AM

Hi,

I am having trouble with comparing some information, and the information it compares it to depends on certain string text it contains.

Breakdown:
Profile (This is a string of text and numbers that defines the size and type of steel, beam, column etc.)
Grade (Material the Profile is made off)
Job Grade (The Grade required for the profiles, this is split into sections, Hot Rolled steel will have a grade which will apply for all profiles starting with a prefix of UA, EA, UB etc.)

Problem Example:

If Cell A2 contained a Profile UB310*46, I want a cell to look at a table that will identify it as 'Hot Rolled Steel' which will then compare the Grade to the Job Grade for 'Hot Rolled Steel', if the Grade and Job Grade is the same return Yes, if they do not match return No.

Attached is an excel sheet to help clarify my query in greater detail.

Don't Hesitate to ask if confused in any way.

Good luck and thanks in advance

Rob

**JBeaucaire** · 07-08-2013, 01:16 AM

If you don't mind TRUE/FALSE instead of YES/NO, then put this in C16 and copy down:

=VLOOKUP(A16, SDEDATA_Checking!D:E, 2, 0)=B16

**Rjwilliams93** · 07-08-2013, 01:39 AM

This is kind of what I was looking for, apart from it is checking the Model Grade against itself and not the Typical standard grades. The Model grade on SDEDATA_Checking wants to compare with the typical Standard grades grade on QA1001, and depending on the profile prefix depends on which grade it compares to in the table. The explanation tab just had everything on one sheet to help with the explanation of what I am trying to achieve.

If the profile starts with UB, UC, EA, UA, WB, WC, or PFC then this makes it Hot Rolled Steel, which means the Model grade wants to compare with the Hot Rolled Steel Grade from the "Typical Standard Grades" table (QA1001 Tab), If it matches then return Yes, is not then No (True or False will also work for me too)

Sorry if I did not make this clear
Cheers

Rob

**JBeaucaire** · 07-08-2013, 10:12 AM

Ok, now that I understand the tests better, hopes for a simple formula have evaporated.

I reformatted your Prefix Table sheet so the data is complete and in standard format table. With that in place, we have to test each code to see if it's a 2-prefix match, a 3 prefix match, or then separately test if its CHS and check the numbers that follow that prefix.

=IF(ISNUMBER(MATCH(LEFT(A2,2), 'Prefix Table'!$B$2:$B$7, 0)), B2=300,
IFERROR((INDEX('Prefix Table'!$C$8:$C$13, MATCH(LEFT(A2, 3), 'Prefix Table'!$B$8:$B$13, 0))=B2),
IF(LEFT(A2,3)="CHS", IF(MID(A2,4,4)+0<168, B2='Prefix Table'!$C$14, B2='Prefix Table'!$C$15))))

**Rjwilliams93** · 07-08-2013, 06:58 PM

Much Appreciated,

This works perfectly, I have tested it with all possible profiles for the prefixes listed.

This is a lot more easier to manage then what I was planning to do.

Cheers

Rob