Using Lookup & Match functions

[dekiv]

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

 

[rondeondo]

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

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

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]

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]

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.

 

[rondeondo]

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]

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

Thanks for your help anyway rondeondo

 

[rondeondo]

sweet, glad I could help, if I did...