# match data within a range

#### kmlynn1010

##### Board Regular
Hey there,

Alright. So, I've got a spreadsheet with thousands of addresses on it. The house number is in column K, the street name in column M and zip code in column R. Every week, I send out letters to new addresses, and some of these addresses do not have zip codes. Is there any formula I can use to find a zip code based on a close address in the first spreadsheet?

For example: an address on my new sheet may be 115 Main St. Is there a way to look for an address between 100 and 120 Main Street and return the zip code?

So far, I've tried MATCH, VLOOKUP, INDEX, in combinations, and I can't seem to get it.

Thanks!!!

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi kmlynn1010

Code:
``=INDEX(\$R1:\$R1000,MATCH(1,(\$M\$1:\$M\$1000="Main St.")*(\$K\$1:\$K\$1000>=100)*(\$K\$1:\$K\$1000<=120),0))``
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Please adapt the ranges inside the MATCH function but don't use whole columns.

Hope this helps
PGC

Hey PCG,

Thanks so much for your help! I think I'm almost there. It seems that I got it to work earlier, but when I was tweaking it, it won't work any more.

The formula returns the first zip code, then the second, and so on without matching the address. Here is how I've tweaked it:

=INDEX('[master mailing list.xls]Sheet1'!\$T\$2:\$T\$26655,MATCH(1,(\$M\$2:\$M\$1000=M2)*(\$K\$2:\$K\$1000>=K2-5)*(\$K\$2:\$K\$1000<=K2+5),0))

I've also tried changing the MATCH part to MATCH(0,(\$M\$2:\$M\$1000=M2)*(\$K\$2:\$K\$1000>=K2-10)*(\$K\$2:\$K\$1000<=K2+10),0)). That seems to be searching for a match, but I'm not sure what value its picking up because when I check it, it's not the correct value.

Do you know what I'm doing wrong?

Thanks!

Got it!!

How silly am I??? I forgot to change the columns to the other spreadsheet, and I still had it on the new sheet.

It works!!

You're brilliant!!!! Thank you! Thank you! Thank you!

Hi kmlynn1010

I'm glad it works! Sorry I didn' answer but I was out.

Cheers.
PGC

Perhaps you could also try this formula where X2 is the number. It should pick up the zip code for the closest number on Main St

=LOOKUP(2,1/(IF(\$M\$1:\$M\$1000="Main St.",ABS(\$K\$1:\$K\$1000-X2))=MIN(IF(\$M\$1:\$M\$1000="Main St.",ABS(\$K\$1:\$K\$1000-X2)))),\$R1:\$R1000)

confirmed with CTRL+SHIFT+ENTER

Hi Barry

Thanks for stepping in and posting a much better solution.

Hi again kmlynn1010

Be sure to try Barry's solution. It's a much better solution than mine. You just specify the house number and it looks for the closest match without you having to supply the max and min values.

Using Barry's idea with the syntax of my previous formula, you may also want to try:

Code:
``=INDEX(\$R8:\$R1007,MATCH(MIN(IF(\$M\$8:\$M\$1007="Main St.", ABS(\$K\$8:\$K\$1007 - X2))), (\$M\$8:\$M\$1007="Main St.")*ABS(\$K\$8:\$K\$1007-X2),0))``

with the house number in X2.

Kind regards
PGC

Replies
13
Views
827
Replies
4
Views
283
Replies
6
Views
2K
Replies
0
Views
374
Replies
5
Views
479

1,217,440
Messages
6,136,630
Members
450,022
Latest member
Joel1122331

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back