substitute for VLOOKUP??

[Jaguar]

Hi,

The VLOOKUP function is :

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

In one sheet, I have a table which contains 31 columns (31 days in a month), and I need to enter the vlookup function in another sheet to lookup values based on the 31 columns.....I have to manually change the col_index_num for each column (ie .....2, 3,......31).

To change each formula for all 31 columns manually becomes a real pain!!.

Is there some other way I can do this ??

Thanks
Jaguar

 

[Aladin Akyurek]

It would be so much easier on you if you also specify the whole range of the lookup table including column headers, the sheet that houses that table, and even the cell which houses the lookup table.

 

[Fergus]

How many columns in your other sheet? Is it 31? and are they contiguous? If so try using the COLUMN() function with either a + or - a number in your VLOOKUP formula, that way you only have to write it once and copy across.

 

[fairwinds]

Hi,

You could use the COLUMN function to give you the col_index_num

E.g if your first formula goes in column B you could have

COLUMN()-COLUMN($A$1)

as col_index_num.

An INDEX/MATCH combination with relative references in the INDEX range should also work.