Using a function with numbers & text

  • Thread starter Thread starter Cardslinger
  • Start date Start date
C

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
 
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.
 
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
 
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.
 
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.
 
Back
Top