ELSIF statements...?

I've been struggling with what I'm sure is a simple problem.

I want to do the following:
I have two Columns/Fields with values in one ranging from '1-3' and values in the other ranging from 'A-E'
I've created a new column Field called RESULTS made to show either U, X or I

So what I'm trying to do is shown in the matrix below:

If the first Field = 1 and the second Field = A then make Field 'RESULTS' = U.
And so on...

0001002003
A00U00U00U
B00U00U00U
C00U00X00X
D00U00X00I
E00U00X00I

I've tried some VBA below, but I can't get it to work.

---------------------------------------------
if [field1] = 0 then
result = "<Null>"

elseif [field1] = 1 then
result = "U"

elseif [field1] = 2 then
if [field2] = "1" or [field2] = "2" then
result = "U"
else
result = "X"

elseif [field1] = 3 then
if [field2] = "1" or [field2] = "2" then
result = "U"

elseif [field2] = "3" then
result = "X"
else
result = "I"

end if

end if

end if

end if

end if

---------------------------------------------

Any advice would be much appreciated.

Perhaps the attached example will work for you - it is based on a VLookup table as opposed to VBA.

Originally Posted by Søren Larsen

Your first range, the one containing 1-3, is a row right?

And to echo JieJenn: When do you want to return X and I? I could probably get it from your code, but just to be sure!

Ah, o.k. I'll try to explain. Got to get my head round this, too...
I've got a very large database of up to 500,000 rows where I need to make a new column revel the results of certain combinations.
So, one column (Letters) has 5 categories A-E. The 2nd column (Numbers) has 3 categories. The 3rd column (RESULTS) with have 3 categories U,X and I

The Matrix thing reveals the combination results:
0001002003
A00U00U00U
B00U00U00U
C00U00X00X
D00U00X00I
E00U00X00I

So, if Col 'Letters' = D and Col 'Numbers' = 3, Col 'RESULTS' = I

Or so I'm trying to achieve, anyway.

Originally Posted by Canuck Chuck

Perhaps the attached example will work for you - it is based on a VLookup table as opposed to VBA.

Wonderful! A lookup table! I should have known.

Big Thank You!

The nearest to the scripting method I've got too is this:

if [field1] = 0 then
result = "<Null>"

elseif [field1] = 1 then
result = "U"

elseif [field1] = 2 then
if [field2] = "A" or [field2] = "B" then
result = "U"
else
result = "X"

end if

elseif [field1] = 3 then
if [field2] = "A" or [field2] = "B" then
result = "U"

elseif [field2] = "C" then
result = "X"
else
result = "I"

end if

end if