You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ken Mock" wrote in message
...
This works perfectly! Follows the expansion and returns the corrected
Value.
Thanks for the Advice!

"T. Valko" wrote:

If you have descriptive column headers you can match the column header.

......A.....B.....C.....D
1..........Ja....Fe....Ma
2...x.....5......3......2
3...y.....4......2......1
4...z.....1......1......1

Lookup Y and return the value from column Fe:

=VLOOKUP("y",A1:D4,MATCH("Fe",A1:D1,0),0)

--
Biff
Microsoft Excel MVP


"Ken Mock" wrote in message
...
Looking for a way to vary the returned value column in a Vlook-up
function. I
have a report that can be run for a various #of financial periods the
Value
returned is the Sum of the Expansions. For each period where data
exists
you
get a column expansion between the lookup value and the returned Value.
For
Example Look-up is column 1, returned Value could be in Column 2-10. I
tried
using the =column()function to identify the column # in a seperate cell
and
link the Vlook-up to that cell instead ofinserting a number, I also
tried
nesting the function inside the V-look-up formula.
Original Formula
=IF(ISERROR(VLOOKUP(C12,C25:P26,13,FALSE))=TRUE,0, VLOOKUP(C12,C25:P26,13,FALSE))
Attempted:
=IF(ISERROR(VLOOKUP(C12,C25:P26,J$4,FALSE))=TRUE,0 ,VLOOKUP(C12,C25:P26,J$4,FALSE))
- where J$4 =Column() which did return the correct column # on
expansion
because the cell is located to the right of columns inserted by the
expansion

=IF(ISERROR(VLOOKUP(C12,C25:P26,(Value(J$4)),FALSE ))=TRUE,0,VLOOKUP(C12,C25:P26,(Value(J$4)),FALSE))

=IF(ISERROR(VLOOKUP(C12,C25:P26,(column()),FALSE)) =TRUE,0,VLOOKUP(C12,C25:P26,(column()),FALSE))
It happens that the formula is going on the same column being returned
in
this Case

=IF(ISERROR(VLOOKUP(C12,C25:P26,(Value(column())), FALSE))=TRUE,0,VLOOKUP(C12,C25:P26,(Value(column() )),FALSE))

all with no luck.
I know this can be done with a macro but I am trying to avoid this.

If anyone can help, I would appreciate it.

thanks