Forum Discussion

DebS32's avatar
DebS32
Copper Contributor
Sep 24, 2020

Matching two ranges

I'm working with two worksheets in a single workbook - 

I have a column of unique IDs (B) in worksheet CA Comp  and need to add the Region number for each

In worksheet County Reg, I have a Column with the unique IDs (B), a Column with the County Name (C) (where there are multiple rows per county), column K has eliminated the duplicates and has each county name listed once and column L (12) is the corresponding region number for the county. I need to put the county number on worksheet CA Comp.....

=IFERROR(INDEX(county_reg!A:E,(MATCH($B2,county_reg!B:B,0),MATCH(county_reg!K2:K60,county_reg!C:C,0)),12),"") 

I get the "problem with this formula" error, but then when I go through the pieces everything adds up - I think the issue is that it doesn't like the range for the first argument for the second match, -- any ideas? 

Formulas and Functions

10 Replies

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Sep 24, 2020

    Hello DebS32,

     

    It appears that the root of your problem is the following section of the formula:

    "(MATCH($B2,county_reg!B:B,0),MATCH(county_reg!K2:K60,county_reg!C:C,0))"

    Perhaps you could elaborate on what you're trying to achieve in this section?

     

    It appears that you are trying to match multiple criteria for the row number but I am not sure if this is what you intended.

    • DebS32's avatar
      DebS32
      Copper Contributor
      Sep 24, 2020

      PReagan 

      yes that's what I need to do - see my response to Sergei for the explanation

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 24, 2020

    DebS32 

    First argument in second match shall be scalar value, not the range. Plus it's not clear what you'd like to return by it, see formatted

    =IFERROR(
   INDEX(
       county_reg!A:E,
      (MATCH($B2,county_reg!B:B,0),
       MATCH(county_reg!K2:K60,county_reg!C:C,0)
      ),
   12),
"")

     

    Plus it's better to use INDEX(E:E,...) instead of INDEX(A:E,...12).

    • DebS32's avatar
      DebS32
      Copper Contributor
      Sep 24, 2020

      SergeiBaklan 

      I'm sorry I don't follow -- 

      the data range for the index in county_reg is in columns B and C, for comparison to K so making it E:E doesn't help - 

      I think the problem is in the 

      MATCH(county_reg!K2:K60,county_reg!C:C,0)

       but K2:K60 is the list of counties aligned to the region number for each county in column L (12) - and I'm trying to get it to recognize each county listed multiple times in column C against the individual county in column K to put the county number in column L/12 back on the master data file on CA Comp...

      • PReagan's avatar
        PReagan
        Bronze Contributor
        Sep 24, 2020

        DebS32 

         

        I'm sorry, I'm having a difficult time visualizing what you are trying to achieve. Perhaps you could share a sample file of your data set? (removing any sensitive information - of course)

         

        Just as a note, unless you are using MATCH() as an array formula, then the lookup_value must be a single value and not a range.

Resources