VLOOKUP, a table with text and numerics

  • Thread starter Thread starter duncan.edment
  • Start date Start date
D

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
 
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
Click to expand...

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
 
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
Click to expand...

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
Click to expand...

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, 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
 
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."
Click to expand...

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
 
Claus,

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

Cheers once again

Duncan
 
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
 
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
Click to expand...

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
 
Back
Top