Using Lookup & Match functions

dekiv

New Member
Joined
Oct 15, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Aug 15, 2012
Messages
156
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
Joined
Oct 15, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi rondeondo,

Thanks for the reply!

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
Joined
Aug 15, 2012
Messages
156
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
Joined
Oct 15, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
    Constant sheet.jpg
    123.6 KB · Views: 7
  • Solutions to New Store Locations.jpg
    Solutions to New Store Locations.jpg
    249.8 KB · Views: 8
  • Matrix of Suburb Distances.png
    Matrix of Suburb Distances.png
    30.2 KB · Views: 9

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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
Joined
Oct 15, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thanks but I got it sorted, just missed certain values which was causing the error!!

Thanks for your help anyway rondeondo
 

Watch MrExcel Video

Forum statistics

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