match data within a range

kmlynn1010

Board Regular
Joined
Feb 8, 2005
Messages
95
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


Please try

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
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
Hi kmlynn1010

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

Cheers.
PGC
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top