Dynamic col_index_num in VLOOKUP?


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?
 
Ad

Advertisements

C

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.
 
C

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.
 
T

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!')
 
C

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.
 
Ad

Advertisements

S

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top