+ Reply to Thread
Results 1 to 3 of 3

Index Match in an Array 2 Criteria

  1. #1
    Registered User
    Join Date
    11-02-2011
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    52

    Index Match in an Array 2 Criteria

    I am trying to use an index match function in an array to return a value. However, I am getting an error. See example below and the formula I am using.

    1 2 3
    a red orange yellow
    b green blue indigo


    I want to return "Blue"

    I think this requires an array formula with the critiera "b" and "2" so it knows to return Blue any sugguestions on the formula I would use. My 2 criteria index(match) array function returns an #NA error.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Index Match in an Array 2 Criteria

    Dendrinos2,

    Given your data....

    1 2 3
    a red orange yellow
    b green blue indigo

    Assuming that table is in cells A1:D3 with row 1 being headers and column A being headers (so actual data is in cells B2:D3), you can get the "blue" result with this formula:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index Match in an Array 2 Criteria

    Here's another one...

    Data in the range A1:D3...

    .........1............2............3...
    a......red.......orange.....yellow
    b......green....blue.........indigo

    Lookup values:

    F1 = B
    G1 = 2

    Then, this formula entered in H1:

    =VLOOKUP(F1,A1:D3,MATCH(G1,A1:D1,0),0)

    If your column headers really are the numbers 1, 2 and 3 then try this version:

    =VLOOKUP(F1,A1:D3,G1+1,0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1