Match Offset combination

**BRISBANEBOB** · 04-06-2009, 01:45 AM

Can Offset be combined in an Index Match Match formula as per the attached sample?

**DonkeyOte** · 04-06-2009, 02:00 AM

Try to avoid using OFFSET as it is is Volatile... use INDEX

It's not really clear how precise your criteria are... the below bases the criteria as follows:

=INDEX($C$12:$F$19,MATCH("Mike",$C$12:$C$19,0)+2,MATCH(DATE(2009,1,1),$C$11:$F$11,0)+1)

Adapt as required.

**BRISBANEBOB** · 04-06-2009, 02:02 AM

Thanks for that - what do you mean by a 'volatile'?

**DonkeyOte** · 04-06-2009, 02:04 AM

XL has smart calculation engine and only calculates cells affected by last alteration etc... that is with exception of Volatile functions which will calculate regardless... common ex. TODAY(), NOW(), RAND(), OFFSET, INDIRECT etc...

see Charles Williams' site for everything you ever need to know about XL calculation: http://www.decisionmodels.com/calcsecretsi.htm

I would strongly advise a quick read through - even if not all of it makes sense first time around...