Find a number in set of data allowing for tolerance levels and return text

[HelenBunny]

Hi,
Hope you can help, I've spent hours trying to figure this out!

I have a group students and their addresses with a postal code in one set of data. I'm trying to match them to get work experience at one of our stores based on proximity of location. BUT the students addresses wont exactly match the location so I'd like to allow for a tolerance level. Say 3 postal codes variance up or down.

So e.g if Student A lives in a post code 3010 - I'd like to look up the postal code in the stores locations data and if stores postal code is between 3007 - 3013 then return the stores name from the stores location data, else "No Close Match"

So e.g:

[TABLE="width: 335"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Student
[/TD]
[TD]Students Suburb[/TD]
[TD]Postal Code[/TD]
[/TR]
[TR]
[TD]Student 5[/TD]
[TD]Sydney[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]Student 6[/TD]
[TD]Pyrmont[/TD]
[TD="align: right"]2009[/TD]
[/TR]
[TR]
[TD]Student 7[/TD]
[TD]Surry Hills[/TD]
[TD="align: right"]2010[/TD]
[/TR]
[TR]
[TD]Student 8[/TD]
[TD]Caulfield North[/TD]
[TD="align: right"]2011[/TD]
[/TR]
[TR]
[TD]Student 9[/TD]
[TD]Waterloo[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD]Student 10[/TD]
[TD]Paddington[/TD]
[TD="align: right"]2021[/TD]
[/TR]
[TR]
[TD]Student 11[/TD]
[TD]Waverley[/TD]
[TD="align: right"]2024[/TD]
[/TR]
[TR]
[TD]Student 12[/TD]
[TD]Rose Bay[/TD]
[TD="align: right"]2029[/TD]
[/TR]
[TR]
[TD]Student 13[/TD]
[TD]Watsons Bay[/TD]
[TD="align: right"]2030[/TD]
[/TR]
[TR]
[TD]Student 14[/TD]
[TD]Clovelly[/TD]
[TD="align: right"]2031[/TD]
[/TR]
[TR]
[TD]Student 15[/TD]
[TD]Maroubra[/TD]
[TD="align: right"]2035[/TD]
[/TR]
[TR]
[TD]Student 16[/TD]
[TD]Glebe[/TD]
[TD="align: right"]2037[/TD]
[/TR]
[TR]
[TD]Student 17[/TD]
[TD]Leichhardt[/TD]
[TD="align: right"]2040[/TD]
[/TR]
[TR]
[TD]Student 18[/TD]
[TD]Leichhardt[/TD]
[TD="align: right"]2040[/TD]
[/TR]
[TR]
[TD]Student 19[/TD]
[TD]Leichhardt[/TD]
[TD="align: right"]2040[/TD]
[/TR]
[TR]
[TD]Student 20[/TD]
[TD]Birchgrove[/TD]
[TD="align: right"]2041[/TD]
[/TR]
[TR]
[TD]Student 21[/TD]
[TD]Enmore[/TD]
[TD="align: right"]2042[/TD]
[/TR]
[TR]
[TD]Student 22[/TD]
[TD]Camperdown[/TD]
[TD="align: right"]2050[/TD]
[/TR]
</tbody>[/TABLE]


Match to:

[TABLE="width: 348"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Store[/TD]
[TD]Store Suburb[/TD]
[TD]Store Postal Code[/TD]
[/TR]
[TR]
[TD]Store 1[/TD]
[TD]Fannie Bay[/TD]
[TD="align: right"]820[/TD]
[/TR]
[TR]
[TD]Store 2[/TD]
[TD]Broadway[/TD]
[TD="align: right"]2007[/TD]
[/TR]
[TR]
[TD]Store 3[/TD]
[TD]Bondi Junction[/TD]
[TD="align: right"]2022[/TD]
[/TR]
[TR]
[TD]Store 4[/TD]
[TD]Coogee[/TD]
[TD="align: right"]2034[/TD]
[/TR]
[TR]
[TD]Store 5[/TD]
[TD]Balmain[/TD]
[TD="align: right"]2041[/TD]
[/TR]
[TR]
[TD]Store 6[/TD]
[TD]Artarmon[/TD]
[TD="align: right"]2064[/TD]
[/TR]
[TR]
[TD]Store 7[/TD]
[TD]Crows Nest[/TD]
[TD="align: right"]2065[/TD]
[/TR]
[TR]
[TD]Store 8[/TD]
[TD]Chatswood[/TD]
[TD="align: right"]2067[/TD]
[/TR]
[TR]
[TD]Store 9
[/TD]
[TD]West Lindfield[/TD]
[TD="align: right"]2070[/TD]
[/TR]
[TR]
[TD]Store 10[/TD]
[TD]Dee Why[/TD]
[TD="align: right"]2099[/TD]
[/TR]
[TR]
[TD]Store 11
[/TD]
[TD]Gladesville[/TD]
[TD="align: right"]2111[/TD]
[/TR]
[TR]
[TD]Store 12[/TD]
[TD]Cherrybrook[/TD]
[TD="align: right"]2126[/TD]
[/TR]
[TR]
[TD]Store 13[/TD]
[TD]Bossley Park[/TD]
[TD="align: right"]2176[/TD]
[/TR]
[TR]
[TD]Store 14[/TD]
[TD]Erina[/TD]
[TD="align: right"]2250[/TD]
[/TR]
[TR]
[TD]Store 15[/TD]
[TD]Castle Hill[/TD]
[TD="align: right"]2275[/TD]
[/TR]
[TR]
[TD]Store 16[/TD]
[TD]Charlestown[/TD]
[TD="align: right"]2290[/TD]
[/TR]
[TR]
[TD]Store 17[/TD]
[TD]Tamworth[/TD]
[TD="align: right"]2340[/TD]
[/TR]
</tbody>[/TABLE]

Allowing for a vriance in 3 points either way

 

[James006]

Hello Helen,

First of all, let us agree on some assumptions ...

1. The first table is in Sheet 1

2. The second table is in Sheet 2

3. In Sheet 1 , Cell D1, you can add header Close Match

4. In Sheet 1, Cell D2, you can test following Array Formula:

=IF(MIN(ABS((C2-Sheet2!$C$2:$C$18)))<=3,INDEX(Sheet2!$A$2:$A$18,MATCH(MIN(ABS((C2-Sheet2!$C$2:$C$18))),ABS((C2-Sheet2!$C$2:$C$18)),0)),"No Match")

For an Array Formula ... instead of the Enter key ... you need to use simultaneously the three keys : Control Shift Enter

Hope this will help

 

[HelenBunny]

James - you ROCK!

This worked perfectly!

Thank you so much - Much appreciated!
Helen

 

[James006]

James - you ROCK!

This worked perfectly!

Thank you so much - Much appreciated!
Helen

Glad you could fix your problem ... :wink:

Thanks for your Thanks ...