Lookup Value from CONCATENATED string

[wutzke]

I have a sheet that contains values representing pant sizes
ie

501STF28X30
501STF28X32
501STF28X34
501STF30X30
501STF30X32
501STF30X34
501STF32X30
501STF32X32
501STF32X34
501STF34X30
501STF34X32
501STF34X34

The 17th column in that sheet contains the total number of the
specific size of each row.

I created a second worksheet that contains a "matrix" size sheet with
the waist sizes in columns and length in rows

X 28 30 32 34
30
32
34

using
=TRIM(CONCATENATE($A$7,OFFSET(501STF,0,COLUMN()-3),"X",OFFSET
(501STF,ROW()-7,0)))

A7 cell contains the string "501STF". The reference 501STF in the
OFFSET call is the top corner cell of the matrix (containg "X" above).

This returns the style and size in each cell with in the grid. That
works fine, but...
Can I use the string to do a look up from the 1st worksheet to find
the total pairs?

I tried
=VLOOKUP((CONCATENATE($A$7,OFFSET(501STF,0,COLUMN()-3),"X",OFFSET
(501STF,ROW()-7,0))),[501_12012009.xls]Sheet1!$C$463:$C$485,17,TRUE)

but gives me a #N/A

http://spreadsheets.google.com/ccc?key=p5XvDvb4SUVj6ejJEpry2QA
show the matrix grid I'm working on

 

[Pete_UK]

The table you are using in the lookup is only one column wide, i.e. $C
$463:$C$485 - this should be $C$463:$S$485 if you want to get data
from the 17th column.

Also, I presume the file 501_12012009.xls is open - otherwise you will
need the full path in front of the filename.

However, these wouldn't normally give you the #N/A error, so are you
sure that you have exact matches in your lookup table? Perhaps you
have some spaces at the end of your pant size codes?

Hope this helps.

Pete