Assign values for one column to another.

[Celticshadow]

Hi

I have in column T certain numbers and texts that that I require to assign a
value to as below, in the adjacent column. Again any pointers would be much
appreciated.

Kind Regards

Celticshadow

T U

1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
0 10
F 10
UR 10
U 10
R 10
S 10
L 10
P 10
PU 10
BD 10
D 10

 

[Pete_UK]

Well, imagine that two-column table occupies cells Y1:Z20. Put this
formula in U1:

=VLOOKUP(T1,Y$1,Z$20,2,0)

and copy down.

Hope this helps.

Pete

 

[Celticshadow]

Hi Pete

Unfortunately that does not seem to work I either get a n/a or value error.
It may be because I have not explained the situation very well. Column T row
1 downwards could contain any one of the below texts or numbers. Thus if
column T row 1 contained the figure 0 then I would like to be able to assign
the value 10 and if column T row 2 contained the letter u then again I would
like to assign the value 10.


Column T
Row 1
Contains
one of the Value to
below Assign
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
0 10
F 10
UR 10
U 10
R 10
S 10
L 10
P 10
PU 10
BD 10
D 10

Kind Regards

Celticshadow

 

[Pete_UK]

Well, it should have worked if you set it up like this:

T U V W X Y Z
x v ? ? ? '1 1
'2 2
'3 3
'4 4
'5 5
'6 6
'7 7
'8 8
'9 9
'0 10
F 10
UR 10
U 10
R 10
S 10
L 10
P 10
PU 10
BD 10
D 10

where x is a value that you put in T1, v is the vlookup formula I gave
you, and ? means anything. Notice that the numbers in column Y are
preceded by an apostrophe - this will turn them into text and will not
be displayed in the cell. You should format column T as General. You
can improve on the formula in U1 with this:

=IF(ISNA(VLOOKUP(T1&"",Y$1,Z$20,2,0)),"",VLOOKUP(T1&"",Y$1,Z$20,2,0))

Just copy this down column U for as many values as you have (or
expect) in column T.

But if you don't want to have a table in Y1:Z20, you could do it like
this in U1:

=IF(T1="","",IF(ISNUMBER(T1),IF(T1>9,"",IF(T1=0,10,T1)),IF(ISNA(MATCH(T1,
{"F","UR","U","R","S","L","P","PU","BD","D"},0)),"",10)))

then copy this down. This (and the other formula) will give a blank
cell if T1 is not one of the acceptable values in your list.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, I just noticed I had put a comma instead of a colon in the
VLOOKUP formula (and earlier) - it should be this:

=IF(ISNA(VLOOKUP(T1&"",Y$1:Z$20,2,0)),"",VLOOKUP(T1&"",Y$1:Z$20,2,0))

Hope this helps.

Pete

 

[Celticshadow]

Hi Pete

I cant access my home PC until later on this evening so I shall let you know
later how I got on, just thought I should mention incase you thought I was
being ignorant. Many Thanks

Kind Regards

Celticshadow

 

[Pete_UK]

That's alright, and I shall be away from my PC from lunchtime and all
day tomorrow, so I won't be able to get back to you until Friday
anyway.

Pete

 

[Celticshadow]

Hi Pete

That works an absolute treat, delighted with the result. Thanks very much
indeed. Just about to post another question. It is quite addictive this
little knowledge base.

Thanks again

Kind Regards

Celticshadow

 

[Pete_UK]

You're welcome - thanks for the feedback.

Pete