Forum Discussion

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

Formulas and Functions

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

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.

DebS32
Copper Contributor
Sep 24, 2020
PReagan
is there a better function for matching a range of data?
- PReagan
  Bronze Contributor
  Sep 24, 2020
  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"?
  - DebS32
    Copper Contributor
    Sep 24, 2020
    PReagan
    Thank you so much - turns out a nested vlookup got me what I needed!
DebS32
Copper Contributor
Sep 24, 2020
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