converting text to numbers 2

[itsfisko]

Hi I need to convert text in on cell to a number in another.I Have tried to use the formula below, but it ony allows 8 functions any ideas need 16+ functions.


=IF(A20="3c",2,IF(A20="3b",3,IF(A20="3a",4,IF(A20="4c",5,IF(A20="4b",6,IF(A20="4a",7,IF(A20="5c",8,IF(A20="5b",9,))))))))
any ideas? thanks crew.

Some lead, some follow....I just Hope!

 

[bluedragon2]

why don't you create a lookup array.

two columns one with the condition and the other with the desired output. then your formula will look like:

=VLOOKUP(A1,<array>,2,0)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[itsfisko]

Thanks now testing this method.

Some lead, some follow....I just Hope!

 

[itsfisko]

mmmm stuck with how to set up.
you say put condition ie:- 3c in one column
and output ie:-3 in the other
then in a third?=VLOOKUP(A1,<array>,2,0)
is this correct

Some lead, some follow....I just Hope!

 

[Bowers74]

If you only have a &quot;Mini Table&quot; of 16+ criteria and these criteria don't change too often, there is no need to create a table at all to run VLOOKUP()

=VLOOKUP(A1,{&quot;3a&quot;,4;&quot;4c&quot;,5;&quot;4b&quot;,6;&quot;4a&quot;,7;&quot;5c&quot;,8;&quot;5b&quot;,9},2,0)

will work just fine for the criteria that you have listed so far, you just have to add to the formula for what you need.

TIP: commas separate column values and semi-colons separate row values.

EXAMPLE: {&quot;CellA1&quot;,&quot;CellB1&quot;;&quot;CellA2&quot;,&quot;CellB2&quot;;&quot;CellA3&quot;,&quot;CellB3&quot;} will create a &quot;Table&quot; like the one below:

     A       B
   CellA1  CellB1
   CellA2  CellB2
   CellA3  CellB3

You just have to try to visualize the table while creating it. ;-)

Good Luck!



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Bowers74]

The traditional way of creating a VLOOKUP() formula is as follows:

     A       B       C
-------------------------
1|   3a  |   4   | Tree
-------------------------
2|   3b  |   5   |  Car
-------------------------
3|   3c  |   6   |  Dog
-------------------------
4|   4a  |   7   |  Cat
-------------------------
5|   4b  |   8   | Bird

The formula =VLOOKUP(&quot;3c&quot;,A1:C5,2,0) will return 6

The VLOOKUP() syntax is as follows:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Look in Excel Help for more details, or run a Keyword search in this forum (find Tab above).

Good Luck!



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[itsfisko]

Sweet!! Thanks guys vlookup was the way to go and Bowers74s method is perfect thanks to you both for your prompt useful excellent help.

Some lead, some follow....I just Hope!

 

[Bowers74]

Glad we could help and Thanx for the

!!!

Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884