|
VLOOKUP, a table with text and numerics
Strange title, but an equally strange problem.
I have a named range, Master_Fees, that is set out as follows: Quantity code: 6 characters max. It may be less than 6, but not less than 5. Formatted as 'General'; Fee: Numeric, formatted to 2 decimal places; Formatted fee: This if formatted into 6 digits, with leading '0' characters as appropriate. For example, if Fee was 6.95, this field would hold "000695". This is presently calculated as =(TEXT(D3*100,"000000")) I have another column, outwith the named range above, that contains the following: Treatment 1...Treatment 10: Each of the cells under these headings will contain the following- A five or six character code, that is one of the 'Quantity Codes' listed in the above range; Two characters, as a combination of 'Y' & 'N', which are always present; A maximum of 32, 2 digit numbers; So what do I want to do? For each entry under 'Treatment', I want to lookup the value that represents the first 6 characters / digits of the 'Treatment', in the named range, and return the fee and / or the formatted fee. I then want to total up each of the Fee values that have been returned, format it as the formatted fee value, and store it in a cell. Simple? I'm banging my head here, as I can't get it. I have, as an example: Named range: 102101 39.30 003930 102102 39.30 003930 102103 39.30 003930 140109 77.85 007785 140110 86.50 008650 140111 95.15 009515 Treatment 1: 102101NN253685 Treatment 2: 140111YN1236524586842341424641 Treatment 3: 102103NN286512 In this case, the result would be: Treatment 1: Fee=39.30 Formatted Fee=003930 Treatment 2: Fee=95.15 Formatted Fee=009515 Treatment 3: Fee=39.30 Formatted Fee=003930 Total charge: Fee=173.75 Formatted Fee=017375 How do I do it? Can anyone help? Duncan |
VLOOKUP, a table with text and numerics
On Wednesday, December 12, 2012 4:23:32 PM UTC, Claus Busch wrote: Hi Duncan, Am Wed, 12 Dec 2012 08:06:13 -0800 (PST) schrieb : Named range: 102101 39.30 003930 102102 39.30 003930 102103 39.30 003930 140109 77.85 007785 140110 86.50 008650 140111 95.15 009515 Treatment 1: 102101NN253685 Treatment 2: 140111YN1236524586842341424641 Treatment 3: 102103NN286512 the string for Treatment 1 is in J3 Then for Fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Maste r_Fees,2,0) and for formatted fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Maste r_Fees,3,0) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 On Wednesday, December 12, 2012 4:23:32 PM UTC, Claus Busch wrote: Hi Duncan, Am Wed, 12 Dec 2012 08:06:13 -0800 (PST) schrieb : Named range: 102101 39.30 003930 102102 39.30 003930 102103 39.30 003930 140109 77.85 007785 140110 86.50 008650 140111 95.15 009515 Treatment 1: 102101NN253685 Treatment 2: 140111YN1236524586842341424641 Treatment 3: 102103NN286512 the string for Treatment 1 is in J3 Then for Fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Maste r_Fees,2,0) and for formatted fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Maste r_Fees,3,0) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Claus, many thanks for the speedy reply. I don't mean to be a pain, but could you assist in explaining what you just did there! =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),MASTE R_FEES,2,0) The 'LOOKUP' itself, LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))). If I'm reading it right, it is looking up the value '9^9' in the lookup vector '1*LEFT($J3,COLUMN(1:1))'. What does the '9^9' signify? To me it looks like 9*9*9*9*9*9*9*9*9, but it can't be. This value doesn't exist in the spreadsheet! Where / what are you getting it from? The Lookup Vector of '1*LEFT($J3,COLUMN(1:1))', to me reads as: get the leftmost 'COLUMN(1:1)' characters from the value in the cell $J3. When I evaluate 'COLUMN(1:1)', it always returns '1'. So, it now says, "return the 1*1 characters from the left of the contents of cell $J3." From my reading, we now have: VLOOKUP(LOOKUP(387420489,1*1),MASTER_FEES,2,0) I don't understand what the formula is trying to do? I could just say thanks for that and move on, however I'd really like to know what it's doing. Duncan |
VLOOKUP, a table with text and numerics
Claus, many thanks for the speedy reply. I don't mean to be a pain, but could you assist in explaining what you just did there! =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),MASTE R_FEES,2,0) The 'LOOKUP' itself, LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))). If I'm reading it right, it is looking up the value '9^9' in the lookup vector '1*LEFT($J3,COLUMN(1:1))'. What does the '9^9' signify? To me it looks like 9*9*9*9*9*9*9*9*9, but it can't be. This value doesn't exist in the spreadsheet! Where / what are you getting it from? The Lookup Vector of '1*LEFT($J3,COLUMN(1:1))', to me reads as: get the leftmost 'COLUMN(1:1)' characters from the value in the cell $J3. When I evaluate 'COLUMN(1:1)', it always returns '1'. So, it now says, "return the 1*1 characters from the left of the contents of cell $J3." From my reading, we now have: VLOOKUP(LOOKUP(387420489,1*1),MASTER_FEES,2,0) I don't understand what the formula is trying to do? I could just say thanks for that and move on, however I'd really like to know what it's doing. Duncan |
VLOOKUP, a table with text and numerics
|
VLOOKUP, a table with text and numerics
Claus,
Not sure I fully understand it, yet, but I'll keep looking at it! Cheers once again Duncan |
VLOOKUP, a table with text and numerics
Claus,
I've been having a look at it and expanding on it. What I have is the following: Columns A B C D E F Code1 Value Code2 Value Code3 Value 010101NN 140132NN 147006NN In the value column, I'm looking to put your VLOOKUP solution however, I'm struggling with it. When I paste the solution into the various columns / rows, the "COLUMN(1:1)" function changes and goes awry. In Cell B2, it has the value "LOOKUP(1:1)" but in B3, it changes to "LOOKUP(2:2)", in B4, it becomes "LOCATION(3:3)" and so on. In Cell D2, it has the value "LOOKUP(1:1)" but again increments by one on each row. The result of this is that it is giving me an error, namely the location function showing as "LOCATION(#REF!)" or the cell showing the value "#N/A". I don't know why, as it works in some cells but not in others, which is making it more and more frustrating. Any help appreciated. Duncan |
VLOOKUP, a table with text and numerics
Hi Duncan,
Am Thu, 13 Dec 2012 08:07:47 -0800 (PST) schrieb : A B C D E F Code1 Value Code2 Value Code3 Value 010101NN 140132NN 147006NN to get the number from A2, format the cell "000000" and try: =LOOKUP(9^9,1*LEFT(A2,COLUMN(1:1))) or without formatting you have to use: =TEXT(LOOKUP(9^9,1*LEFT(A2,COLUMN(1:1))),"000000") You can put your VLOOKUP aroundto find the value. It doesn't mind if COLUMN(1:1) changes to COLUMN(2:2) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
| All times are GMT +1. The time now is 08:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com