# Matching two ranges

Highlighted
Occasional Contributor

# 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?

10 Replies
Highlighted

# Re: Matching two ranges

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

Highlighted

# Re: Matching two ranges

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.

Highlighted

# Re: Matching two ranges

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

Highlighted

# Re: Matching two ranges

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

Highlighted

# Re: Matching two ranges

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.

Highlighted

# Re: Matching two ranges

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

Highlighted

# Re: Matching two ranges

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

Highlighted

# Re: Matching two ranges

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"?

Highlighted

# Re: Matching two ranges

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

Highlighted

# Re: Matching two ranges

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