[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Excel function #NA error dropdownlist with array

dear experts i have this problem i'm using a index combined with match to return a value. based on a drop down list with years. it works for 2012 but when i change the year in the dropdown list i get a #NA error.

see attached example file for a better understanding;)

=INDEX('Exp Prod type'!$E$2:$E$61,MATCH(1,('Exp Prod type'!$A$2:$A$61='Overview Renewables'!$A4)*('Exp Prod type'!$B$2:$B$61='Overview Renewables'!$A$2),0))

Open in new window

thanks in advance


1 Solution
barry houdiniCommented:
You need to match against the month in column D rather than column A, try this version in B4 confirmed with CTRL+SHIFT+ENTER and copied down

=INDEX('Exp Prod type'!$E$2:$E$61,MATCH(1,('Exp Prod type'!$D$2:$D$61=$A4)*('Exp Prod type'!$B$2:$B$61=$A$2),0))

regards, barry
MarkVrenkenAuthor Commented:
Great thanks:) it worked like a charm

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now