Dynamic col_index_num in VLOOKUP?

  • Thread starter Thread starter tb
  • Start date Start date
T

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?
 
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?
Click to expand...

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


Regards
Claus B.
 
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?
Click to expand...

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.
 
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.
Click to expand...

Works great! Thanks for your help.

PS: I think that Sheet1! needs to be enclosed with single quotes.
('Sheet1!')
 
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!')
Click to expand...

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


Regards
Claus B.
 
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?
Click to expand...

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

San
 
Back
Top