Sep 24 2020 08:54 AM
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?
Sep 24 2020 09:05 AM
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).
Sep 24 2020 09:09 AM
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.
Sep 24 2020 09:13 AM
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...
Sep 24 2020 09:14 AM
yes that's what I need to do - see my response to Sergei for the explanation
Sep 24 2020 09:27 AM
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.
Sep 24 2020 09:35 AM
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
Sep 24 2020 09:38 AM
is there a better function for matching a range of data?
Sep 24 2020 09:49 AM
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"?
Sep 24 2020 10:18 AM
Thank you so much - turns out a nested vlookup got me what I needed!
Sep 24 2020 10:39 AM