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:

Student	Students Suburb	Postal Code
Student 5	Sydney	2000
Student 6	Pyrmont	2009
Student 7	Surry Hills	2010
Student 8	Caulfield North	2011
Student 9	Waterloo	2017
Student 10	Paddington	2021
Student 11	Waverley	2024
Student 12	Rose Bay	2029
Student 13	Watsons Bay	2030
Student 14	Clovelly	2031
Student 15	Maroubra	2035
Student 16	Glebe	2037
Student 17	Leichhardt	2040
Student 18	Leichhardt	2040
Student 19	Leichhardt	2040
Student 20	Birchgrove	2041
Student 21	Enmore	2042
Student 22	Camperdown	2050

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Match to:

Store	Store Suburb	Store Postal Code
Store 1	Fannie Bay	820
Store 2	Broadway	2007
Store 3	Bondi Junction	2022
Store 4	Coogee	2034
Store 5	Balmain	2041
Store 6	Artarmon	2064
Store 7	Crows Nest	2065
Store 8	Chatswood	2067
Store 9	West Lindfield	2070
Store 10	Dee Why	2099
Store 11	Gladesville	2111
Store 12	Cherrybrook	2126
Store 13	Bossley Park	2176
Store 14	Erina	2250
Store 15	Castle Hill	2275
Store 16	Charlestown	2290
Store 17	Tamworth	2340

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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