Matching two ranges

Copper Contributor

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? 

10 Replies

@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).

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.

@Sergei Baklan 

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 

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

@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.

@PReagan 

yeah so that's what I thought, I'm working in 365 and hitting shift+control doesn't add the {} for an array - 

CA Comp

col B     col F

ID         Reg

1

2

3

4

 

 

County_Reg

Col B       Col C          Col D             Col K     Col L

ID              county        org             county    region

123                A              A1                 A              1

223                A              A2                 B              2

324                B               B1                C             3

452                B               B2                 D            4

 

@PReagan 

is there a better function for matching a range of data? 

@DebS32 

 

Is column F of "CA Comp" the column that you are trying to fill with this formula?

Is this column supposed to be the region # from column L of "County_Reg" that correlates with the adjacent county from column K of "County_Reg"?

@PReagan 

Thank you so much - turns out a nested vlookup got me what I needed!

@DebS32 

 

Wonderful! Glad that you were able to figure it out!