auto pick up info from different sheets by conditions

hi,
could anyone help me with the below query?

I'll need to pick up the rates from sheets "SYD" & "BNE" by info from column A of "Summary", which means if the info in column A is "sydney", the formula should pick up the rates from sheet "SYD", if it's "brisbane", the formula should pick up the rates from sheet "BNE".
Also, the rate should be matched by the column B,C & D from "summary" page.

Thanks,
M

Originally Posted by JBeaucaire

I always always caution people against putting on two sheets for reference what should and can be on one sheet for reference. Copy the data from the Brisbane sheet onto the Sydney sheet below the Sydney data and you have one perfect table that can grow as long as it needs to and is easy to maintain. Rename that sheet "Rates".

For the PerKg table, only list the START of each tier in E3:G3, so that would be 0, 500, 1000.

Then the formulas would be:

E2: =VLOOKUP($C2, Rates!$A:$D, 4, 0)
F2: =INDEX(Rates!$E$4:$G$12, MATCH($C2, Rates!$A$4:$A$12, 0), MATCH($D2, Rates!$E$3:$G$3, 1)) * $D2

Thank you so much for the help!
An issue with the formula,the row match "MATCH($C3, Rates!$A$4:$A$12, 0)" should match by both column B&C, which is Destination Suburb +Post code, would you be able to get the worked?

I got interrupted by lunch, but here's an alternative. You need to change the values in E3:G3 of the SYD and BNE sheets to 0, 500, 1000, as Jerry advised, then you can have this formula in E2 of the Summary sheet:

=INDEX(INDIRECT("'"&IF(A2="Sydney","SYD","BNE")&"'!E:G"),MATCH(C2,INDIRECT("'"&IF(A2="Sydney","SYD","BNE")&"'!A:A")),MATCH(D2,INDIRECT("'"&IF(A2="Sydney","SYD","BNE")&"'!E3:G3")))

and then copy this down.

Not sure why you need to match on postcode and Suburb - isn't the postcode fairly unique?

Hope this helps.

Pete

thanks pete, the formula is working

thkanks,ChemistB, it works

With the source data, the same post code could have several different suburbs, but seems all are at the same rate.
So for the time being, i will leave it as it is. Thanks

Originally Posted by JBeaucaire

Well, I know you could "invent" data that showed this need, but does your real data actually have that need? I've never seen a postal chart (and I've made many) that needed anything other than the zip code to determine the rate. You have "zip codes" that have multiple rates within that one zip?

I believe in simple. Look at the INDIRECT() formulas required to keep the separate sheets, compared to the ones used on the merged sheet I suggested? We can continue to make formulas more and more complicated, but only if 100% necessary. Is it? Really? Check your real data.