Excel function #NA error dropdownlist with array

Posted on 2012-09-17
Last Modified: 2012-09-17
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


Question by:MarkVrenken
    LVL 50

    Accepted Solution

    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
    LVL 1

    Author Closing Comment

    Great thanks:) it worked like a charm

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now