Using a function with numbers & text

[Cardslinger]

I have 6 columns, B has numbers + text (3d, 6c, ad, kd) C has numbers + text
( 3 down, 6 cube, alternate dialog) I want to input into D 6c and have the
return in E be 6 cube, or ad input/alternate dialog output.
I have over 60 input/output codes.
I tried the IF function but it won't work with text. Is there a function
that will accomplish this?
TIA

 

[Dave]

Hi,
If you have over 60 I/O codes, you should probably use VLOOKUP.
If it doesn't already exist, write a 2-column table of input/output codes.
In C1, enter =IF(D1="","",VLOOKUP(D1,AA1:AB60,2,0))
(Where AA1:AB60 is your table - change to suit)
Then copy down.

 

[Cardslinger]

Dave
Thanks for the response, I used your code and - AA1:AB60 had to be changed
to A1:B60 or it gave an error.
Even with the changes when I copy down the code increases the input (D1) as
it should but it also increases the A1:B60 also. (A2:B61 etc). I tried
inserting $ ! to the A1:B60 code to make those constant but that wont work.
Anyway to do this?

BTW my name is Dave, good name!!!
Thanks

 

[Dave]

Hi,
Does that mean your table range is A1:B60?

Sorry about missing out the $'s. Try this, and copy down.
=IF($D1="","",VLOOKUP($D1,$A$1:$B$60,2,0))

The $A$1:$B$60 will lock the table reference, and the $D1 will lock the
column, but allow the row to increment.

Regards - Dave.

 

[Cardslinger]

That code worked thanks so much for the help
Happy Fathers Day
Dave

 

[Dave]

You're welcome. Thanks for the feedback.
Dave.

 

[Cardslinger]

One more quick question if you don't mind?
As I only have to input 2 charactors is there a way to format the column to
automatically jump to the next cell as soon as i input the 2nd charactor??
That would save time and as i'm doing readouts and inputs I wont forget to
hit tab/return.