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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

kmlynn1010

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

kmlynn1010

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi kmlynn1010

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

Cheers.
PGC
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,136,926
Messages
5,678,616
Members
419,776
Latest member
mikelowski

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
Top