Dynamic col_index_num in VLOOKUP?
 

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?

--
tb

Dynamic col_index_num in VLOOKUP?
 

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Dynamic col_index_num in VLOOKUP?
 

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Dynamic col_index_num in VLOOKUP?
 

On 8/20/2014 at 9:25:18 AM Claus Busch wrote:

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

Dynamic col_index_num in VLOOKUP?
 

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Dynamic col_index_num in VLOOKUP?
 

On Wednesday, August 20, 2014 7:32:47 PM UTC+5:30, tb wrote:
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?



--

tb

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

San