Formula for classification system

[Dean]

Hi all,

Essentially what I am after is a formula, which I am assuming will
have to include VLOOKUP at some point, that will allow me to classify
data into a five character classification system.

A spreadsheet with the data can be found here:
http://spreadsheets.google.com/ccc?key=pIZmEkZZWJ0g3zkJbqI-EPA

The first worksheet 'Class test' has my sample data, and the second
worksheet 'Class VLOOKUP' is the actual data definitions. In column F-
H on worksheet 2 are example codes derived from that actual data
definitions. By using the data located in columns A-G on worksheet 1 I
would like to end up with that 5 character classification in column H.

As before, I would be very grateful if you could offer any assistance
regarding this. If you have further questions. please let me know.

Thanks in advance,
Dean

 

[John]

Hi Dean
What you want can be done but with some modification to your form and
descriptions.
=VLOOKUP('Class test'!A3,C28,2,FALSE) will give you the first letter "T"
=VLOOKUP('Class test'!B3,C1117,2,FALSE) will give you your second letter
"W" and you can see how that works.
Problems: What lookup is reading on one page, it's got to be the same
wording on the second page eg: Roughcst,etc "A4" on first page lookup on
second page is what ?
maybe C4 but the software can't guess that. You need to put the same wording
on both forms.
I changed column B information to column D. that's for VLOOKUP to work ok
I found lots of empty space at the beginning and after your descriptions,
are you importing this information or are you typing it. You will get #NA
error if you leave those empty space .
Last you can CONCATENATE all letters this way =I3&J3&K3&L3&M3. Each letter &
number is your VLOOKUP cell, you would type this in "Class test H Column.
HTH
John

 

[Dean]

Hi Dean
What you want can be done but with some modification to your form and
descriptions.
=VLOOKUP('Class test'!A3,C28,2,FALSE) will give you the first letter "T"
=VLOOKUP('Class test'!B3,C1117,2,FALSE) will give you your second letter
"W" and you can see how that works.
Problems: What lookup is reading on one page, it's got to be the same
wording on the second page eg: Roughcst,etc "A4" on first page lookup on
second page is what ?
maybe C4 but the software can't guess that. You need to put the same wording
on both forms.
I changed column B information to column D. that's for VLOOKUP to work ok
I found lots of empty space at the beginning and after your descriptions,
are you importing this information or are you typing it. You will get #NA
error if you leave those empty space .
Last you can CONCATENATE all letters this way =I3&J3&K3&L3&M3. Each letter &
number is your VLOOKUP cell, you would type this in "Class test H Column.
HTH
John

Thanks for your help John. As you suggested, once the spaces were
removed all of the formuals retuned the correct data. Well done with
this, I guess I was over-thinking it; your solutions are quite simple
(in hindsight).

Thanks again,
Dean

 

[John]

You're Welcome

Hi Dean
What you want can be done but with some modification to your form and
descriptions.
=VLOOKUP('Class test'!A3,C28,2,FALSE) will give you the first letter "T"
=VLOOKUP('Class test'!B3,C1117,2,FALSE) will give you your second letter
"W" and you can see how that works.
Problems: What lookup is reading on one page, it's got to be the same
wording on the second page eg: Roughcst,etc "A4" on first page lookup on
second page is what ?
maybe C4 but the software can't guess that. You need to put the same
wording
on both forms.
I changed column B information to column D. that's for VLOOKUP to work ok
I found lots of empty space at the beginning and after your descriptions,
are you importing this information or are you typing it. You will get #NA
error if you leave those empty space .
Last you can CONCATENATE all letters this way =I3&J3&K3&L3&M3. Each letter
&
number is your VLOOKUP cell, you would type this in "Class test H Column.
HTH
John

Thanks for your help John. As you suggested, once the spaces were
removed all of the formuals retuned the correct data. Well done with
this, I guess I was over-thinking it; your solutions are quite simple
(in hindsight).

Thanks again,
Dean