Text to represent numbers

Darrin Smith

New Member
Joined
Sep 29, 2006
Messages
12
I am wanting to use text inputs to alter numbers in a formula.

i.e. the numerical value of cell E11 is utilised in a formula. This numerical value is required to change depending on the text entered in cell E10.

i.e.
if HJV entered in E10, E11 should be 669.
if YSL entered in E10, E11 should be 656.
if TOE entered in E10, E11 should be 640.
etc, etc

How to do this?

Rgds
Darrin
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

cmhoz

Active Member
Joined
Aug 20, 2006
Messages
268
Assuming you have more than a few possible entries for E10, the easiest thing to use is a VLOOKUP.

Someplace out of the way, create a list like this

HJV 669
YSL 656
TOE 640

Doesn't matter where you put this list, as long as the stuff you want to look up is in a column to the left of what you want to bring back.

Assuming this list is located on Sheet2 in columns A and B your formula in E11 will look like this:

=VLOOKUP(E10, Sheet2!A:B, 2, FALSE)

=vlookup(what you want to find, where you want to find it, column to bring back, FALSE=only find identical match)

You can use the formula wizard (click the equals sign next to your formula bar) to help.
 

Forum statistics

Threads
1,141,027
Messages
5,703,816
Members
421,318
Latest member
cg_cartoonexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top