Using OFFSET with multi-criteria VLOOKUP with INDIRECT

[HeyInKy]

Here's my (working) formula:

Please Login or Register  to view this content.

This looks up the criteria in FX1 & FX2 on the current sheet and the corresponding sheet name in column A, and then returns the value in the correct cell from that correct sheet... let's say, for example, this returns "54" from cell NorthDistrict!BA35... I now also need the value from the cell 2 rows below, from NorthDistrict!BA37)... I'm assuming an OFFSET will work, but where do I put it in this complex, array of a formula?

P.S. - I can't use the VLOOKUP/CHOOSE because my criteria in cells FX1 and FX2 which would correspond to BA37 are the same in the cells corresponding to BA35 - just a weird way my system converts the data from SQL to Excel Comma Delimited...

[jason.b75]

That formula looks the same as

{=INDEX(INDIRECT($A3&"!BA15:BA45"),MATCH(TRUE,IF(INDIRECT($A3&"!AX15:AX45")=FX$1,INDIRECT($A3&"!AY15:AY45")=FX$2),0))}

Which should be more efficient than your method, and easier to adjust for your new requirement. Changing the range of the first INDIRECT to $A3&"!BA17:BA47") will return the entry 2 rows below.

[HeyInKy]

Like a charm! It'll take me a bit to dissect this and understand what all is happening here, but the fact is it works! Thanks and cheers!