• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Generation of Col_Index_num based on selection


Active Member
Hi Excel Experts,

I work with large data sets in excel and very frequently apply the Vlookup formula.

In the Vlookup formula there is an parameter called Col_Index_num, where we key in the result column number.
ex: Columns A:F, we key in as 6 , as need the result from F Column, this range changes from workbook to workbook.
Is there a possibility of auto generating the Col_Index_num (in the vlookup formula) based on the last column selected.


Peter Bartholomew

Well-Known Member
If the column has a header that is the same from sheet to sheet, MATCH will search the column headers and return the column number. The number can then be used for the return array in VLOOKUP or, better, combined with the row-wise MATCH as parameters of INDEX.

GraH - Guido

Well-Known Member
Why not use match () that returns a position of a range. If you have the same column headers it might be something like:
= VLOOKUP ( key , LookupRange, MATCH (Header, HeaderRange, 0) , 0 )

EDIT: beaten by Peter (again :))

Peter Bartholomew

Well-Known Member
Guido, Sorry. At least we are in agreement!

If it were for me, I would use an Excel Table and XLOOKUP, so my views probably don't count for anything!

GraH - Guido

Well-Known Member
Last time I checked, XLOOKUP is not yet available in the armoury. I'm not one of those guys who can't stand VLOOKUP (). I use it as frequent as I do use index/match combo. It depends on the situation and ease of formula writing, Peter.