Hi Duncan,

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

=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."

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2