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


Who is Participating?
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.