# Using Lookup & Match functions

#### dekiv

##### New Member
Hi guys,

I have a very complicated question that I need help with.

Basically, I am trying to work out the potential customers for suburbs based on their distance to other suburbs and the rules that apply. So in short:
1. Need to work out a suburbs distance from one to another (e.g. Annerley to Bardon) that is located on another sheet (all distances have already been calculated between suburbs).
2. Then use that to apply the rules which is (if a suburb is within 2km of one another then it is 5% of potential customers)
3. The potential customers for each suburb are also on another sheet.

So for example:
Albion to Annerley is 4km, which means Albion is only entitled to receive 5% of the potential customers from Annerley (where Annerley has 4579 customers. So it would be 5% of that figure. I have something that is close but is not returning a true value (e.g. if suburbs are over 5km away, they receive 0%, so answer should be 0) but mine is not returning that.

This is what I have:
=IFERROR(VLOOKUP(INDEX('Matrix of Suburb Distances'!\$A\$3:\$AM\$43,MATCH(\$A4,'Matrix of Suburb Distances'!\$A\$3:\$A\$43,0),MATCH(E\$3,'Matrix of Suburb Distances'!\$A\$3:\$AM\$3,0)),Constant!\$AD\$6:\$AE\$9,2,TRUE),Constant!\$AE\$6)*VLOOKUP(\$A4,Constant!\$AG:\$AK,COLUMNS(Constant!\$AG\$3:\$AK\$3),0)

Any help would be appreciated

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### rondeondo

##### Board Regular
Hi dekiv
can you please post some data or give an idea of the layout, my initial look suggests the problem may be in the second match
Excel Formula:
``=IFERROR(VLOOKUP(INDEX('Matrix of Suburb Distances'!\$A\$3:\$AM\$43,MATCH(\$A4,'Matrix of Suburb Distances'!\$A\$3:\$A\$43,0),MATCH(E\$3,'Matrix of Suburb Distances'!\$A\$3:\$A\$48,0)),Constant!\$AD\$6:\$AE\$9,2,TRUE),Constant!\$AE\$6)*VLOOKUP(\$A4,Constant!\$AG:\$AK,COLUMNS(Constant!\$AG\$3:\$AK\$3),0)``
I'm guessing that your distance matrix has the postcodes or suburb names across the columns A to AM and also in A4 to A43. So you need to get one suburb from the columns and the other from the rows to get the distance at their intersection. my suggestion above for changing the second match to be looking through the list of rows.
For now I'm not looking at your constants sheet as you haven't said anything about that.

changed \$A\$3:\$AM\$3 to \$A\$3:\$A\$48

#### dekiv

##### New Member
Hi rondeondo,

I've tried your formula but it is still not spitting out the correct figure.

It would be a lot easier to demonstrate if I could attach the sheet!

You'll see that in the "Solutions to New Store Locations" sheet that is where I'm trying to come up with the answer by using the "matrix of suburb distances sheet" to determine the distance and then compare that to the "constant" sheet. The rules are in columns AC-AE of the constant sheet that relate to the percentage dependant on the distance between suburbs. Column AK in the constant sheet shows the potential customers for each suburb.

I know that Albion to Annerley is 8.94km which according to the rules is over 5km - meaning that should be 0.

Thanks for the help!

#### rondeondo

##### Board Regular
there is a plugin you can download using the button labelled as L2BB. This allows you to paste in a page from your excel file.
When I'm building something with this level of complexity, I split the formula up so I can see which bit is not doing as I expect. I can't see anything in your formula relating to the distance returned with a >+ or a <= 5.

#### dekiv

##### New Member

Yeah I don't know where to put it really - also I was told that the formula should be efficient and that lookups and match are the main functions.

I've attached some screenshots of the sheets to shed some light.

#### Attachments

• Constant sheet.jpg
123.6 KB · Views: 7
• Solutions to New Store Locations.jpg
249.8 KB · Views: 8
• Matrix of Suburb Distances.png
30.2 KB · Views: 9

#### rondeondo

##### Board Regular
if you have a dropbox or google drive you can share a link and I can take a look later tonight. I need the file to split the formula up to see what each bit does. I am quite comfortable chunking formulas up but it's hard starting from one when it's not working as intended.

#### dekiv

##### New Member
Thanks but I got it sorted, just missed certain values which was causing the error!!

Thanks for your help anyway rondeondo

#### rondeondo

##### Board Regular
sweet, glad I could help, if I did...

Replies
1
Views
76
Replies
6
Views
8K
Replies
23
Views
2K
Replies
0
Views
307
Replies
3
Views
467

1,127,632
Messages
5,625,990
Members
416,149
Latest member
Bigpotato 668

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

### Which adblocker are you using?

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

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