+ Reply to Thread
Results 1 to 2 of 2

INDEX, MATCH, INDIRECT (multiple columns)

  1. #1
    Registered User
    Join Date
    02-20-2020
    Location
    MA
    MS-Off Ver
    2007
    Posts
    3

    INDEX, MATCH, INDIRECT (multiple columns)

    HI all, first post long time creeper.

    I am working on a data sheet where the first sheet is a entry template and the other sheets are data tables for the entry template to pull data from

    On this entry sheet is BOX1 that is a drop down list with the names of the other sheets/ data tables.
    Column 2(sheet1) is a code (references column A of sheet from Box 1)
    Column 3(sheet1) is a 2 digit code (references column B of sheet from Box 1)
    Column 4(sheet1) is a price that is populated by the criteria entered from Column 2 and 3

    Column 4(sheet1) should grab a price from column 3(tablesheet) if there is only data in Column 2(sheet1) . It should grab a price from Column 4(tablesheet) is there is data in Column2(sheet1) and Column3(sheet1).

    Example: I want to search the price of (59020)(00) and it returns 100
    Now I want to search (59020)(10) and it returns 120.

    I have had success running this formula to return data correctly for one column, but cannot get it to search for the additional column 2.

    Here is the formula : =INDEX(INDIRECT("'"&$B$4&"'!C:C"),MATCH(C5&D5,(INDIRECT("'"&$B$4&"'!A:A")=C5)*(INDIRECT("'"&$B$4&"'!B:B")=D5),0))

    HELP. Please and thank you.

    Also the working formula that only uses one column of data currently looks like
    =INDEX(INDIRECT("'"&$B$4&"'!C:C"),MATCH(C6,INDIRECT("'"&$B$4&"'!A:A"),0))
    Last edited by mxalex229; 02-20-2020 at 03:51 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,568

    Re: INDEX, MATCH, INDIRECT (multiple columns)

    Try this, entered as an ARRAYED function
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

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

+ 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] Data Extract from Multiple sheets with indirect index match function
    By Elcoyote in forum Excel General
    Replies: 19
    Last Post: 05-30-2019, 01:08 AM
  2. Replies: 1
    Last Post: 09-21-2017, 05:35 PM
  3. [SOLVED] Extracting information from multiple tables (Vlookup, Index match, indirect??)
    By vesper007 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2016, 02:36 AM
  4. index match with indirect across multiple sheets
    By mamachrissy1028 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-13-2015, 08:43 PM
  5. INDEX MATCH and INDIRECT to pull in data from multiple worksheets
    By nebshaver123 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-16-2015, 03:58 PM
  6. Replies: 8
    Last Post: 10-14-2014, 01:54 AM
  7. index match - using indirect and automating the Columns
    By etaf in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 02-06-2014, 07:15 AM

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