Search code examples
excelexcel-formulareferencearray-formulascell-array

Is there a simple way to have a cell reference that is included in a formula increment vertically while copying formula horizontally?

I want to be able to copy my formula from cell B4 into cells C4,D4,etc. so that the cell reference to B18 changes to B19, B20, etc. and the table column continues to reference [date].

I've tried referencing the cells in different ways, but I am having no such luck. When I try to drag the formula horizontally from cell B4 to cell C4 the reference to cell $B18 doesn't change but the table column that's being referenced changes from [date] to [platform].

The formula is =IFERROR(INDEX(Table1,MATCH($B18,Table1[date],0),2),"")

Does anyone know how I can adjust my formula to do this, or is there a different way to write it all together to make this easier?

Screenshot of excel sheet

Solution

  • You can lock cell references with a double bracket for table columns [[field]:[field]]

    =IFERROR(INDEX(Table1,MATCH($B18,Table1[[date]:[date]],0),2),"")