Search code examples
excelexcel-formulaoffice-2016

Build index match in excel with dynamic sheet

I'm trying to build an INDEX MATCH combo with dynamic sheet input and 3 variables for matching.

I leveraged this article to get an understanding on how to do it and I opted to go for the non-array version (or more like index native array I guess)

I ended up with the below cell-function, currently only incorporating 2 variables.

PROBLEM: This currently returns a Value Not Available Error. I've double checked the input multiple times and cannot seem to find the issue with the formula.

Current formula for clarity:

=INDEX(
INDIRECT(D2&"!J2:J20000");
MATCH(1;
(B1=INDIRECT(D2&"!E2:E20000"))*(B3=INDIRECT(D2&"!G2:G20000"));
0))

enter image description here

Solution

  • In case of Excel-2016 you need to entry the formula as array literal. Means, Press CTRL+SHIFT+ENTER after putting the formula in cell. So, when you put the formula as array entry it will add {...} curly bracket before and after the formula. The formula will look like

    {=INDEX( INDIRECT(D2&"!J2:J20000"); MATCH(1; (B1=INDIRECT(D2&"!E2:E20000"))*(B3=INDIRECT(D2&"!G2:G20000")); 0))}