help with offset and vlookup

[gfalc3194]

I am trying to combine an offset function with a vlookup. The formula
I've entered is as follows:
=offset((VLOOKUP((LEFT(M2571,7)),'[testRED04.XLS]DUBE'!$A$4:$a$1067,1,false)15,5))

I need to take only the 7 leftmost characters in cell m2571, lookup
the value in the file testRED04, then use an offset from there.

The offset alone works fine if I hardcode where to look. The Vlookup
works fine on its own. What is wrong with my combo?

Thanks

 

[Harlan Grove]

I am trying to combine an offset function with a vlookup. The formula
I've entered is as follows:
=offset((VLOOKUP((LEFT(M2571,7)),'[testRED04.XLS]DUBE'!$A$4:$a$1067,1,false)
15,5))

Please paste in the EXACT formula you're trying. It's obvious the formula above
isn't it because (1) offset would be capitalized and (2) the absence of a comma
between the right parenthesis and 15 is a syntax error that would prevent the
formula from being entered in the cell.

If your formula were (removing unnecessary and evidently confusing parentheses)

=OFFSET(VLOOKUP(LEFT(M2571,7),'[testRED04.XLS]DUBE'!$A$4:$A$1067,1,false),15,5)

Then what's wrong is that OFFSET requires a *range* as its first argument, but
the result of VLOOKUP is *never* a range, only a value. That so, I'd guess you
want either

=OFFSET('[testRED04.XLS]DUBE'!$A$4:$A$1067,
MATCH(LEFT(M2571,7),'[testRED04.XLS]DUBE'!$A$4:$A$1067,0)+15,5,1,1)

or

=INDEX('[testRED04.XLS]DUBE'!$F$4:$F$1067,
MATCH(LEFT(M2571,7),'[testRED04.XLS]DUBE'!$A$4:$A$1067,0)+15)

The latter has the advantage of still returning the intended result rather than
a #REF! error if the testRED04.XLS file were closed.