Vlook-up in report with column Expansion

[Ken Mock]

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,C2526,13,FALSE))=TRUE,0,VLOOKUP(C12,C2526,13,FALSE))
Attempted:
=IF(ISERROR(VLOOKUP(C12,C2526,J$4,FALSE))=TRUE,0,VLOOKUP(C12,C2526,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,C2526,(Value(J$4)),FALSE))=TRUE,0,VLOOKUP(C12,C2526,(Value(J$4)),FALSE))

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

=IF(ISERROR(VLOOKUP(C12,C2526,(Value(column())),FALSE))=TRUE,0,VLOOKUP(C12,C2526,(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

 

[Luke M]

In your original formula, you reference the 13th column. However, column J is
the 10th column. since you're having problems, I would recommend trying the
following:

=IF(ISERROR(VLOOKUP(C12,C2526,COLUMN(M1),FALSE))=TRUE,0,VLOOKUP(C12,C2526,COLUMN(M1),FALSE))

Remember that COLUMN returns the value of column in all of XL, where in
VLOOKUP your wanting the column reference relative to the given array. If you
need to use column J, but still want a value of 13, use:
VLOOKUP(C12,C2526,COLUMN(J1)+3,FALSE)

to adjust.

 

[T. Valko]

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",A14,MATCH("Fe",A11,0),0)

 

[Ken Mock]

Originally the report was built referencing column 13, as they users ran this
on a standard 3 period range, expanding the returned information into the
same column every time. The location could be hardcoded as 13. Recently they
have started using variable comparisons 2-6 periods which is why I now have
to find a way to work out identification of the column rather than a fixed
number.

Thanks

 

[Ken Mock]

This works perfectly! Follows the expansion and returns the corrected Value.
Thanks for the Advice!

 

[T. Valko]

You're welcome. Thanks for the feedback!