Help with excel formulas

FinallyStoppedLurking

Hope this is the right place to ask, apologies if not. I realise there are specific excel forums out there but thought I'd try my luck here to save registering elsewhere. I need to create an excel document where if a text value is entered into 1 cell then the adjacent cell has a numerical value. The table below shows how the text values and the the numerical value I would like them to represent. 

Text Value          Numerical Value
One1                  1
One2                  2
One3                  3
Two1                  4
Two2                  5
Two3                  6
Etc

In total there will be 77 different text values. If the cell with the text value is blank I'd like the adjacent cell to remain blank as well.

Le_Kirk

You might want to look at Nested IF statements.  Does the text value always remain the same?  Possibly a Vlookup table might be worth a look.  Once you have sorted out the lookup table, you would use an IF statement to say IF value in text cell = "", put "" in Numerical value cell, else, if it has a value, revert to the lookup table.
BTW there are quite a few Excel forums that will give you an answer by searching without the need to register.

FinallyStoppedLurking

Thank you. Managed to find a nested formula that works but will look into the Vlookup table.

Heedtheadvice

Several ways do it but to find the best way....it depends upon what end result you want to achieve...

For example:

Assuming column A has the text/blank entries and column B the numerics...

Do you want col B cells to hold any numeric value or increasing in value from 1 as shown, only incrementing when there is text (blank does not cause an increment in number) or just any old number? Are you trying to count the number of textual entries or something else, does the number need to filled in when the text value is one of the 77 only?

Giving some guidance as to what you are trying to achieve should alter the approach such as if you are looking to count the number of matching entries in col A that agree with your 77 or some other reason. There are several ways to do what you specifically ask but any suggestion might not be the best to get to your overall aim so clarification would help.

FinallyStoppedLurking

Hi, thanks for your reply.
The table in my post was just to show how the text may correspond to a value. The idea is that you would put value anyone of the text values in a cell and the corresponding number would appear in the adjacent cell.

I have managed to get this to work using an if function: =IF(A1="One1",1,IF(A1="One2",2,IF(A1="One3",3)))
The issue with this is that it requires me to make a really long IF function that will take hours.

I've just played around with the Vlookup using =VLOOKUP(D1,A1:B6,2,FALSE) With this, column A has the text values, column b has the corresponding number. 

The Vlookup solution is better for me as it means I can change the text value or corresponding number easily in the future. The only issue I am having is that if I drag the above formula into D2 then the whole formula changes to =VLOOKUP(D2,A2:B7,2,FALSE) This no longer works as the table is still in the range A1:B6.

Is there a way to avoid the above or a better solution altogether? I could of course manually change the formula but this would seem an inefficient way of doing things. 

Cornucopia

This is a good approach, but you just need to stop Excel automatically changing your table reference.

You can do this by adding "$" to the front of the thing you want to stay static. 

e.g.   VLOOKUP(D1,$A$1:$B$6,2,FALSE)

wongataa

FinallyStoppedLurking said:

The Vlookup solution is better for me as it means I can change the text value or corresponding number easily in the future. The only issue I am having is that if I drag the above formula into D2 then the whole formula changes to =VLOOKUP(D2,A2:B7,2,FALSE) This no longer works as the table is still in the range A1:B6.

Is there a way to avoid the above or a better solution altogether? I could of course manually change the formula but this would seem an inefficient way of doing things. 

Use $ to lock the cell range - if you change A1:B7 to $A$1:$B$7 it will not change the range when you copy the formula to another cell.  Using $ before the column letter will keep the column the same during any formula copying. Using $ before the row number will keep the row number the same during any formula copying.  Using $ before the column and row identifiers will prevent the cell reference changing during any formula copying.

FinallyStoppedLurking

Thanks both, really helpful. 

stragglebod

Use a named range or =VLOOKUP(D1,$A$1:$B$6,2,FALSE)

Were_Doomed

If there is no corresponding value returned by the VLOOKUP you'll get a #N/A result. You can handle this by preceding the VLOOKUP formula by using:

=IF(ISNA(VLOOKUP(etc.)),"The value you want to show for a #N/A result",VLOOKUP(etc.))

Sandtree

Were_Doomed said:

If there is no corresponding value returned by the VLOOKUP you'll get a #N/A result. You can handle this by preceding the VLOOKUP formula by using:

=IF(ISNA(VLOOKUP(etc.)),"The value you want to show for a #N/A result",VLOOKUP(etc.))

This is how most people write the above but I wonder why the below formula isnt more commonly used? In principle it feels as if it should be more efficient....

=IFERROR(VLOOKUP(etc),"The value you want to show for a #N/A result")