Re: Offset/match returns #value error

Thanks Biff

This works really well in most cases. However, where OFFSET has been used to
return an array of values rather than a single value, can INDEX be used the
same way? For example, in the following formula OFFSET is used to return a
7x8 array.

=VLOOKUP("Word",OFFSET('[0208hi.xls]WKSHT_NAME'!$A$8,MATCH($A7,'[0208hi.xls]WKSHT_NAME'!$A$9:$A$132,0),1,7,8),8,FALSE)

I know INDEX can return an entire row or column by setting the column or row
argument to 0, but can it take say addresses of the top left and bottom right
cell of an array and then return the entire array?

Cheers
Geoff
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"T. Valko" wrote:

Use INDEX instead of OFFSET.

I don't know how your row offset of MATCH()+4 relates to C8 but you can
probably figure it out.

--
Biff
Microsoft Excel MVP


"Geoff" <Geoff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9D08836F-AD1E-4566-AD58-64FD7155593D@xxxxxxxxxxxxxxxx
The following function looks up a value in another workbook:

=OFFSET('PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$C$8,MATCH($A11,'PATH\Testing\2008hi\[0208hi.xls]WKSHT_NAME'!$A$9:$A$96,0)+4,0)

Unfortunately when that particular workbook is closed, the function
returns
a #VALUE! error - so the referenced spread*** needs to be open for the
formula to be evaluated. I could replace all the formulae with values but
I
would like to preserve the reference to the other spread*** in the cells
if
at all possible. Any suggestions?

--
There are 10 types of people in the world - those who understand binary
and
those who don't.



.

Loading