Thanks Roger,

That worked a treat. I will need to name range the row and column headers.

Cheers.
TT

"Roger Govier" wrote:

Hi Trevor

I would be inclined to use Index Match for my result, rather than a mix
of Vlookup and Match
Regardless of the shape of your table, if the top left corner is always
A1 then
=INDEX(NAME_RANGE,MATCH("Total",A:A,0),MATCH("YY", (1:1),0))

If your NAME_RANGE does not always start in A1, then I would have 2
other named ranges,
Myrow which defined the row with the headings (e.g. $7:$7),
and Mycol which defined the column with the names and Total (e.g. $C:$C)
Then
=INDEX(NAME_RANGE,MATCH("Total",Mycol,0),MATCH("YY ",Myrow,0))

--
Regards

Roger Govier


"trevor_tito" wrote in message
...
Hi,

I am trying to determine the column index number in a vlookup with a
matcxh
function but I'm getting an #N/A error message.

The table I am looking up may change in shape (e.g. not always the
same
number of rows or columns) so I have given this a name range.

The table (called NAME_RANGE) is basically

ZZ YY XX
A 10 40 70
B 20 50 80
C 30 60 90
TOTAL 60 150 240

The formula I am using is more or less:

=VLOOKUP("TOTAL",NAME_RANGE,MATCH("YY",NAME_RANGE) ,FALSE)

I should get 150.

What have I done wrong?

Cheers
TT