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!
Bookmarks