Dynamic col_index_num in VLOOKUP?

[tb]

The syntax for VLOOKUP is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Is there a way to have a dynamic "col_index_num" instead of a static
value?

For instance, in the formula:
=VLOOKUP($A15,'Sheet1'!$A:$AT,20,0)
is there a way to make the "20" a dynamic value such that if I add or
delete columns before the 20th one then the col_index_num will adjust
itself accordingly?

 

[Claus Busch]

Hi,

Am Wed, 20 Aug 2014 14:02:47 +0000 (UTC) schrieb tb:

The syntax for VLOOKUP is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Is there a way to have a dynamic "col_index_num" instead of a static
value?

search for the column header of column 20:
=VLOOKUP(A1,Sheet1!A:AT,MATCH("Header20",Sheet1!$1:$1,0),0)


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Wed, 20 Aug 2014 14:02:47 +0000 (UTC) schrieb tb:

The syntax for VLOOKUP is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Is there a way to have a dynamic "col_index_num" instead of a static
value?

or try it with:
=INDEX(Sheet1!T:T,MATCH(A1,Sheet1!A:A,0))
Index(Sheet1!T:T will be automatically adjusted when inserting a column


Regards
Claus B.

 

[tb]

Hi again,


or try it with:
=INDEX(Sheet1!T:T,MATCH(A1,Sheet1!A:A,0))
Index(Sheet1!T:T will be automatically adjusted when inserting a
column


Regards
Claus B.

Works great! Thanks for your help.

PS: I think that Sheet1! needs to be enclosed with single quotes.
('Sheet1!')

 

[Claus Busch]

Hi,

Am Wed, 20 Aug 2014 18:51:06 +0000 (UTC) schrieb tb:

PS: I think that Sheet1! needs to be enclosed with single quotes.
('Sheet1!')

the single quotes are only needed if you have spaces in the sheet name


Regards
Claus B.

 

[San]

The syntax for VLOOKUP is as follows:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])



Is there a way to have a dynamic "col_index_num" instead of a static

value?



For instance, in the formula:

=VLOOKUP($A15,'Sheet1'!$A:$AT,20,0)

is there a way to make the "20" a dynamic value such that if I add or

delete columns before the 20th one then the col_index_num will adjust

itself accordingly?

Thanks tb and Claus for the question and the answer.. I was facing the same problem..!

San