# INDEX MATCH - increase column reference by 1 as formula extended

1. ## INDEX MATCH - increase column reference by 1 as formula extended

Hi all,

I'm using an Index Match function in one sheet to bring in values from another sheet. I want to copy the formula across to many additional columns so that the formula works to import multiple columns of data. Unfortunately, I can't seem to get the 'column' part of the formula to work so that it increases by one every time I drag the formula across. Is there a quick fix for this?

The formula I have is:

=INDEX('Sheet 2'!\$A\$1:\$F\$7,MATCH(Sheet1!\$A2,'Sheet 2'!\$A:\$A,0),2) and I want the column to be relative so it increases by 1 as I drag across. Or, if there's something even more clever than this to account for columns that don't match across sheets, even better, but I did line them up in preparation by moving some columns.

I have tried using the 'column' function (using something like column x - column y +1) in place of the 'column' number but it didn't seem to work. I have seen other options with 'offset' formulas but again it didn't work, perhaps because it's more applicable to a vlookup than an index match?

I have attached a workbook example to illustrate the data and formula in with simple dummy data.

Grateful for any suggestions to crack this one

Thanks as always!

2. ## Re: INDEX MATCH - increase column reference by 1 as formula extended

Hi,
Try this in B2 and across:
=INDEX('Sheet 2'!\$A\$1:\$F\$7,MATCH(Sheet1!\$A2,'Sheet 2'!\$A:\$A,0),COLUMNS(\$A:B))

3. ## Re: INDEX MATCH - increase column reference by 1 as formula extended

Try this:

=INDEX('Sheet 2'!\$B\$2:\$F\$7,MATCH(Sheet1!\$A2,'Sheet 2'!\$A\$2:\$A\$7,0),MATCH(B\$1,'Sheet 2'!\$B\$1:\$F\$1,0))

4. ## Re: INDEX MATCH - increase column reference by 1 as formula extended

thanks Belinda and Ali, both are good solutions and are working

There are currently 1 users browsing this thread. (0 members and 1 guests)