Complex Index/Match with possible Offset requirement

[PaulGMD]

Hello,

I attach a workbook demonstrating what I am looking for i.e. I need a formula that can return a specific value based on certain criteria, over numerous columns.

Essentially the formula has to locate the Asset ID, offset that column by -1, check if a specific date is in that offset column, then return the value offsetting that date cell by 7 columns. If that specific date isn't in the column, then it should return zero.

Please let me know if further detail is required.

Thank you!

[Estevaoba]

Try this:

E4=IFERROR(INDEX(OFFSET($I$4:$I$100,,MATCH(B4,$H$1:$AH$1,0)+4),MATCH(A4,OFFSET($H$4:$H$100,,MATCH(B4,$H$1:$AH$1,0)-2),0)),0)

[ben_hensel]

Yeah, formula in D4 and pull down looks like:

Please Login or Register  to view this content.

Big note that OFFSET is a volatile formula -- it recalculates whenever anything changes anywhere in the spreadsheet whatsoever, so it will eat a lot of processing power. If you've got a serious set of data here, the the workbook will get sluggish surprisingly quick. If you've got the ability to compress this 3D table so that Asset ID / Date are two different columns, and then you just crush everything down into one table, than that would be a lot kinder to your computer.

[kvsrinivasamurthy]

Non volatile formula.
In E4 then copied down.

Please Login or Register  to view this content.

[PaulGMD]

Thank you for your assistance everyone!

kvsrinivasamurthy's formula seems to be the least taxing, much appreciated!

[kvsrinivasamurthy]

Thanks for feedback.Pl mark the thread solved.