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