Matching two ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-1709099%22%20slang%3D%22en-US%22%3EMatching%20two%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1709099%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20working%20with%20two%20worksheets%20in%20a%20single%20workbook%20-%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20column%20of%20unique%20IDs%20(B)%20in%20worksheet%20CA%20Comp%26nbsp%3B%20and%20need%20to%20add%20the%20Region%20number%20for%20each%3C%2FP%3E%3CP%3EIn%20worksheet%20County%20Reg%2C%20I%20have%20a%20Column%20with%20the%20unique%20IDs%20(B)%2C%20a%20Column%20with%20the%20County%20Name%20(C)%20(where%20there%20are%20multiple%20rows%20per%20county)%2C%20column%20K%20has%20eliminated%20the%20duplicates%20and%20has%20each%20county%20name%20listed%20once%20and%20column%20L%20(12)%20is%20the%20corresponding%20region%20number%20for%20the%20county.%20I%20need%20to%20put%20the%20county%20number%20on%20worksheet%20CA%20Comp.....%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(county_reg!A%3AE%2C(MATCH(%24B2%2Ccounty_reg!B%3AB%2C0)%2CMATCH(county_reg!K2%3AK60%2Ccounty_reg!C%3AC%2C0))%2C12)%2C%22%22)%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20the%20%22problem%20with%20this%20formula%22%20error%2C%20but%20then%20when%20I%20go%20through%20the%20pieces%20everything%20adds%20up%20-%20I%20think%20the%20issue%20is%20that%20it%20doesn't%20like%20the%20range%20for%20the%20first%20argument%20for%20the%20second%20match%2C%20--%20any%20ideas%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1709099%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1709191%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20two%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1709191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F807678%22%20target%3D%22_blank%22%3E%40DebS32%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%20argument%20in%20second%20match%20shall%20be%20scalar%20value%2C%20not%20the%20range.%20Plus%20it's%20not%20clear%20what%20you'd%20like%20to%20return%20by%20it%2C%20see%20formatted%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20INDEX(%0A%20%20%20%20%20%20%20county_reg!A%3AE%2C%0A%20%20%20%20%20%20(MATCH(%24B2%2Ccounty_reg!B%3AB%2C0)%2C%0A%20%20%20%20%20%20%20MATCH(county_reg!K2%3AK60%2Ccounty_reg!C%3AC%2C0)%0A%20%20%20%20%20%20)%2C%0A%20%20%2012)%2C%0A%22%22)%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlus%20it's%20better%20to%20use%20INDEX(E%3AE%2C...)%20instead%20of%20INDEX(A%3AE%2C...12).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1709216%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20two%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1709216%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F807678%22%20target%3D%22_blank%22%3E%40DebS32%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20appears%20that%20the%20root%20of%20your%20problem%20is%20the%20following%20section%20of%20the%20formula%3A%3C%2FP%3E%3CPRE%3E%22%3CSPAN%3E(MATCH(%24B2%2Ccounty_reg!B%3AB%2C0)%2CMATCH(county_reg!K2%3AK60%2Ccounty_reg!C%3AC%2C0))%22%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3EPerhaps%20you%20could%20elaborate%20on%20what%20you're%20trying%20to%20achieve%20in%20this%20section%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20appears%20that%20you%20are%20trying%20to%20match%20multiple%20criteria%20for%20the%20row%20number%20but%20I%20am%20not%20sure%20if%20this%20is%20what%20you%20intended.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1709281%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20two%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1709281%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sorry%20I%20don't%20follow%20--%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20data%20range%20for%20the%20index%20in%20county_reg%20is%20in%20columns%20B%20and%20C%2C%20for%20comparison%20to%20K%20so%20making%20it%20E%3AE%20doesn't%20help%20-%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20the%20problem%20is%20in%20the%26nbsp%3B%3C%2FP%3E%3CPRE%3EMATCH(county_reg!K2%3AK60%2Ccounty_reg!C%3AC%2C0)%3C%2FPRE%3E%3CP%3E%26nbsp%3Bbut%20K2%3AK60%20is%20the%20list%20of%20counties%20aligned%20to%20the%20region%20number%20for%20each%20county%20in%20column%20L%20(12)%20-%20and%20I'm%20trying%20to%20get%20it%20to%20recognize%20each%20county%20listed%20multiple%20times%20in%20column%20C%20against%20the%20individual%20county%20in%20column%20K%20to%20put%20the%20county%20number%20in%20column%20L%2F12%20back%20on%20the%20master%20data%20file%20on%20CA%20Comp...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

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

Highlighted

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

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

Highlighted

@PReagan 

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

Highlighted

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

Highlighted

@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

 

Highlighted

@PReagan 

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

Highlighted

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

Highlighted

@PReagan 

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

Highlighted

@DebS32 

 

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