+ Reply to Thread
Results 1 to 7 of 7

Using index,match with multiple columns

  1. #1
    Forum Contributor
    Join Date
    06-10-2008
    Location
    SW Ontario, Canada
    MS-Off Ver
    Excel 365
    Posts
    154

    Using index,match with multiple columns

    I've done this before but can't wrap my head around it again.
    I simplified my problem in this pic.
    excelclip_zpsikwippvu.png
    A4 is my dropdown fro the 2 choices in J and L columns
    A9 is a dropdown for the cabinet choices in column H

    SO I need to find the drawer price for a b24, depending on the drawer selection in A4.
    With ISNA function.

    thanks

  2. #2
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Using index,match with multiple columns

    Give this a shot
    =INDEX(J4:L7,MATCH(A9,H4:H7,0),MATCH(A4,J2:L2,0))

    I don't know why you need ISNA?

  3. #3
    Forum Contributor
    Join Date
    06-10-2008
    Location
    SW Ontario, Canada
    MS-Off Ver
    Excel 365
    Posts
    154

    Re: Using index,match with multiple columns

    I'll try that thanks.
    I usual use iserror but in my spreadsheet I often insert more columns when updating so I find that ISNA keeps me from having to modify formulas less often.

  4. #4
    Forum Contributor
    Join Date
    06-10-2008
    Location
    SW Ontario, Canada
    MS-Off Ver
    Excel 365
    Posts
    154

    Re: Using index,match with multiple columns

    Thanks that works for my shortened sample. I'm getting a n/a error yet on my main spreadsheet but I think that's because my names aren't matching.

  5. #5
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Using index,match with multiple columns

    Use Data Validation > List > J2:L2 for the Drawer Selection (A4)
    Use Data Validation > List > H4:H7 for the Cabinet Type (A9)

    This makes sure the user can only enter names that exist, and can use a dropdown to do it

  6. #6
    Forum Contributor
    Join Date
    06-10-2008
    Location
    SW Ontario, Canada
    MS-Off Ver
    Excel 365
    Posts
    154

    Re: Using index,match with multiple columns

    Yes I have those setup. I mean I think there's an issue with my column names not matching in my full spreadsheet.
    Thanks for getting me on the right track though.

  7. #7
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Re: Using index,match with multiple columns

    NP thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Index Match to Sum Multiple Columns
    By phelbin in forum Excel General
    Replies: 5
    Last Post: 09-02-2015, 07:40 PM
  2. [SOLVED] Multiple IF/THEN INDEX MATCH looking across two columns
    By Aquarock in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-11-2014, 03:35 PM
  3. [SOLVED] Index / Match across multiple columns
    By Chad B in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2013, 11:52 AM
  4. INDEX MATCH with multiple columns
    By there83 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2013, 11:33 PM
  5. Index Match multiple columns
    By bmhalula in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-11-2013, 01:06 AM
  6. Index Match Across Multiple Columns
    By FFastZB in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-20-2013, 04:58 PM
  7. [SOLVED] Index/Match from multiple columns
    By hgopp99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-21-2006, 02:15 PM

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