VLOOKUP, a table with text and numerics

[duncan.edment]

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' charactersas 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 inthe 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

 

[Claus Busch]

Hi Duncan,

Am Wed, 12 Dec 2012 08:06:13 -0800 (PST) schrieb (e-mail address removed):

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))),Master_Fees,2,0)
and for formatted fees:
=VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Master_Fees,3,0)


Regards
Claus Busch

 

[Duncan]

Hi Duncan, Am Wed, 12 Dec 2012 08:06:13 -0800 (PST) schrieb (e-mail address removed): > 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 J3Then for Fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Master_Fees,2,0) and for formatted fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Master_Fees,3,0) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

Hi Duncan, Am Wed, 12 Dec 2012 08:06:13 -0800 (PST) schrieb (e-mail address removed): > 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 J3Then for Fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Master_Fees,2,0) and for formatted fees: =VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),Master_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))),MASTER_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

 

[duncan.edment]

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))),MASTER_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

 

[Claus Busch]

Hi Duncan,

Am Wed, 12 Dec 2012 09:20:52 -0800 (PST) schrieb
(e-mail address removed):

=VLOOKUP(LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))),MASTER_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."

column(1:1) gives you 1, 2, 3, ...16384 (1 to columns.count step 1),
that gives you for LEFT($J3,COLUMN(1:1))) e.g 1, 10, 102, 1021, 10210,
102101, 102101N....
and then for 1*LEFT($J3,COLUMN(1:1))) 1, 10, 102, 1021,10210, 102101,
#Value, #Value,... and LOOKUP(9^9,1*LEFT($J3,COLUMN(1:1))) then shows
you the last result 102101.


Regards
Claus Busch

 

[duncan.edment]

Claus,

Not sure I fully understand it, yet, but I'll keep looking at it!

Cheers once again

Duncan

 

[duncan.edment]

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

 

[Claus Busch]

Hi Duncan,

Am Thu, 13 Dec 2012 08:07:47 -0800 (PST) schrieb (e-mail address removed):

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