Can this be simplified

[Jtvining]

=IF(B$1=OFFSET(INDEX('Sheet1'!$A:$O,MATCH($A4,'Sheet1'!$A:$A,0),1),-1,14),INDEX('Sheet2'!$A$1:$I$1000,MATCH($A4,'Sheet2'!$A:$A,0),4),IF(B$1=OFFSET(INDEX('Sheet1'!$A:$O,MATCH($A4,'Sheet1'!$A:$A,0),1),-1,15),INDEX('Sheet2'!$A$1:$I$1000,MATCH($A4,'Sheet2'!$A:$A,0),4),IF(B$1=OFFSET(INDEX('Sheet1'!$A:$O,MATCH($A4,'Sheet1'!$A:$A,0),1),-1,16),INDEX('Sheet2'!$A$1:$I$1000,MATCH($A4,'Sheet2'!$A:$A,0),4),IF(B$1=OFFSET(INDEX('Sheet1'!$A:$O,MATCH($A4,'Sheet1'!$A:$A,0),1),-1,17),INDEX('Sheet2'!$A$1:$I$1000,MATCH($A4,'Sheet2'!$A:$A,0),4),IF(B$1=OFFSET(INDEX('Sheet1'!$A:$O,MATCH($A4,'Sheet1'!$A:$A,0),1),-1,18),INDEX('Sheet2'!$A$1:$I$1000,MATCH($A4,'Sheet2'!$A:$A,0),4),0)))))

Can this be simplified into a shorter formula?

 

[MrKowz]

What is this formula meant to do? Can you please explain the logic in it? (Some sample data will help as well)

 

[Jtvining]

it looks up a date and if it matches puts the corresponding value in the cell. if not it moves to the next column to look for the match and so on and so forth.

the problem is that the lookup date is not a cloumn heading it is entered in a row above what the match brings back that is why the offset of -1.

the formula works but when spread accross the amount of cells i need it really slows down the process

 

[Scott Huish]

The return value is below the date, why not HLOOKUP?

 

[Jtvining]

because hlookup requires a fixed range while the range i am using is dependent on the match in the very first if statment which is then offset to return the date. this date is compared to the heading date on another sheet and if this matches then it pulls the number from the corresponding column on another sheet

 

[MrKowz]

Can you please provide some data so we can easier visualize what your requirements are?

 

[Jonmo1]

Try

=IF(ISNUMBER(MATCH(B$1,OFFSET(INDEX('Sheet1'!$A:$A,MATCH($A4,'Sheet1'!$A:$A,0)),-1,14,1,5),0)),INDEX('Sheet2'!$D$1:$D$1000,MATCH($A4,'Sheet2'!$A:$A,0)),0)