Hey all - Geoff here - brand new to the forum and admittedly a novice-intermediate user of Excel (and its Mac cousin - Numbers). In my web search for the solution to my problem, I have already discovered the VLOOKUP and LOOKUP funtions and have it working. Cool stuff, but it does not quite do what I need.
I need to convert a Price Code consisting of a string of ten letters, each corresponding to a number 0-9, into a currency number. In my case:
P=1, H=2, A=3, R=4, M=5, O=6, C=7, I=8, S=9, T=0
So the price code PHA is equal to 123 or $1.23
I need to create a spreadsheet where I can enter the price codes in one cell and have the corresponding price appear in another cell.
I have created a data table where column A is the letters and column B is the numbers, and I can insert a VLOOKUP function to get a SINGLE number (ie, I can enter "R" in one cell and have "4" appear in the corresponding cell), but I am mystified about how to combine multiple LOOKUPS (e.g how do I enter PHA and have 123 appear?)
HELP!!
Last edited by NBVC; 12-14-2011 at 10:26 AM.
Hi Geoff,
Are you saying a typical price code will be 10 letters long, or 3?
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
Hi - yes, I realized after I stumbled into bed last night that I forgot to include that final requirement in my post:
The formula I am after should contain the appropriate functionality so that it can handle a code input from "P" to "SSSS" (e.g. a price range from $0.01 to $99.99).
In order to "concatenate" lookup results in Excel, without VBA udf's, you need to physically concatenate 4 VLOOKUP results together.
So assuming your code is entered in A1 and your lookup table is in J1:K5, then try:
=(VLOOKUP(MID(A1,1,1),$J$1:$K$5,2,0)&IF(LEN(A1)>1,VLOOKUP(MID(A1,2,1),$J$1:$K$5,2,0),"")&IF(LEN(A1)>2,VLOOKUP(MID(A1,3,1),$J$1:$K$5,2,0),"")&IF(LEN(A1)>3,VLOOKUP(MID(A1,4,1),$J$1:$K$5,2,0),""))/100
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
BEAUTIFUL!!
Not only did I have to look up "concatenate" online, I had to make some adjustments to code to fit my spreadsheet - but it works, and works perfectly... and frankly, I could not recreate that that string if you paid me. :-)
Thanks a million.
-Geoff
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks