0

I have an index match formula that evaluates correctly if I use F9, however when I actually press enter it returns #N/A.

The formula is designed to perform a vlookup on two conditions:

=INDEX(Table2[#All], MATCH(1,([@countries]=Table2[[#All],[countries]])*([@item]=Table2[[#All],[item]]),0),7)

The workbook setup, is two sheets. In Table1, the rows countries and item are being matched to those values in Table2 and then the 7th column extracted and returned.

Can anyone explain what subtlety I'm missing, I've never seen a formula evaluate correctly manually and then break when entered.

Example Sheet

http://www.fileconvoy.com/dfl.php?id=g6b77d4bb9dab75c8999661037709c740263a1b35d

1 Answer 1

1

Your formula is an array formula, you need to finish it by CTRL+ALT+ENTER instead of just ENTER.
So giving the error message is the expected result, apparently excel manage press of F9 in a more intelligent way.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .