match data within a range

[kmlynn1010]

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!!!

 

[pgc01]

Hi kmlynn1010


Please try

=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]

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]

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]

Hi kmlynn1010

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

Cheers.
PGC

 

[barry houdini]

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]

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:

=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