+ Reply to Thread
Results 1 to 6 of 6

Using Indirect References in Index-Match Function is giving me a Reference Error

  1. #1
    Registered User
    Join Date
    01-05-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    30

    Using Indirect References in Index-Match Function is giving me a Reference Error

    Hello,

    I am using the following formula to compare each record of column C and D to the entire columns of both C and D in a referenced workbook.

    After the comparison is made, if there is a match, records in columns E-G of the main work book will be populated with records E-G from the referenced workbook.


    Here is the formula:

    =INDEX(INDIRECT("'["&InputCurTaxRates&"]Sheet1'!$c$2:$g$50000"),MATCH($C3&$D3,INDIRECT("'["&InputCurTaxRates&"]Sheet1'!$c$2:$c$50000&!$d$2:$d$50000"),0),2)


    Notice that I am using a two-column lookup in the lookup_value parameter of the MATCH function. I have tried to do the same with the lookup_array parameter. However, it is giving me a reference error.

    Does anyone have any suggestions?


    Thanks!

    Channing

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,524

    Re: Using Indirect References in Index-Match Function is giving me a Reference Error

    You need to fully reference d2:d50000 with its own INDIRECT.

    Array enter - enter using Ctrl-Shift-Enter

    =INDEX(INDIRECT("'["&InputCurTaxRates&"]Sheet1'!$c$2:$g$50000"),MATCH($C3&$D3,INDIRECT("'["&InputCurTaxRates&"]Sheet1'!$c$2:$c$50000")&INDIRECT("["&InputCurTaxRates&"]Sheet1'!$d$2:$d$50000"),0),2)
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    01-05-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    30

    Re: Using Indirect References in Index-Match Function is giving me a Reference Error

    Bernie,

    Thank you so much for your help! It corrected the Reference Error, but now I'm getting a N/A Error and I'm not sure why.

    Channing

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,524

    Re: Using Indirect References in Index-Match Function is giving me a Reference Error

    Make sure that the values in C3 and D3 are exactly the same as values in your referenced table - do a copy / paste values from your table to those cells as a test - the formula should pick up the other values from the same row. If you are having problems, cut down your data file to, say, 5 rows and post both files here.

  5. #5
    Registered User
    Join Date
    01-05-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    30

    Re: Using Indirect References in Index-Match Function is giving me a Reference Error

    Bernie,

    I still can't seem to figure out the N/A Error. I've attached the spreadsheets. Open to Rates and StateCo File before the Test Model. As the Test Model worksheet is referencing the two others.


    Thanks,
    Channing
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,524

    Re: Using Indirect References in Index-Match Function is giving me a Reference Error

    You, er,.... I forgot a leading single quote in the last Indirect function (sorry )

    INDIRECT("'["&InputCurTaxRates&"]Sheet1'!$d$2:$d$50000"),

+ 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. Replies: 7
    Last Post: 01-10-2017, 11:11 PM
  2. [SOLVED] Index, Match and Indirect giving REF error
    By stanleds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2013, 06:08 AM
  3. [SOLVED] Three questions starting with Index, Match giving #REF! error
    By JO505 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-02-2013, 07:42 PM
  4. [SOLVED] Indirect function giving #REF! error
    By mqdias in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-03-2013, 10:21 AM
  5. INDEX function with nested INDIRECT reference
    By jharris63 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-13-2013, 11:26 AM

Tags for this Thread

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