How can I use a formula to get a value from finding a match in another sheet?

T

Tigerfish

New member
Joined
Aug 26, 2016
Messages
7
Reaction score
0
Points
0
Hi there. I'm trying to set up a formula to determine price based on zone, with the zone determined by finding a location match in another sheet in the document.

Eg. If the data in Sheet 2 cell B2 is found in Sheet 1 Column B, then the answer in Shhet 2 cell C2 should equal Sheet 1 Column D in the same row as the data match found.

Sheet 2:
StreetSuburbZone
Spring Hill

Sheet 1:
PostcodeSuburbKms from CBDZone
4000Spring Hill0.81

Can anyone help with this?
 
So, if you type Spring Hill in the suburb column on sheet 2, you want Excel to automatically put 1 into the zone column - is this right?

Try this in the zone column of sheet 2 copied down:

=VLOOKUP(Sheet2!B2,Sheet1!$B$2:$D$400,3,0)

Change the array length to suit your lookup data.
 
Last edited:
AliGW said:
So, if you type Spring Hill in the suburb column on sheet 2, you want Excel to automatically put 1 into the zone column - is this right?

Try this in the zone column of sheet 2 copied down:

=VLOOKUP(Sheet2!B2,Sheet1!$B$2:$D$400,3,0)

Change the array length to suit your lookup data.
Click to expand...

Thanks heaps AliGW, that worked!

I have one more to add to the mix now. I have the below formula in cell E3 to calculate a price:

=if(D3="cl",214.5, if(D3="cr",77,if(D3="csd",77,if(D3="cs",135,if(D3="auc",35,if(D3="","")) ))))

I need to add to this formula, "if C3 = 1, add 0, if C3 = 2, add 20, if C3 = 3, add 40, if C3 = 4, add 60"

Please note that the value in C3 is the "zone" from my previous post, so the value is the result of a formula to pull the data from another sheet.
 
Multi conditional sum

Tigerfish said:
Thanks heaps AliGW, that worked!

I have one more to add to the mix now. I have the below formula in cell E3 to calculate a price:

=if(D3="cl",214.5,if(D3="cr",77,if(D3="csd",77,if(D3="cs",135,if(D3="auc",35,if(D3="",""))))))

I need to add to this formula, "if C3 = 1, add 0, if C3 = 2, add 20, if C3 = 3, add 40, if C3 = 4, add 60"

Please note that the value in C3 is the "zone" from my previous post, so the value is the result of a formula to pull the data from another sheet.
Click to expand...

Further to the above, I've tried to add the below to my formula:

if(C3="1"+0,if(C3="2"+20,if(C3="3"+40,if(C3="4"+60)) ))

So, my entire formula is a little like this:

=sum(if(D3="cl",214.5, if(D3="cr",77,if(D3="csd",77,if(D3="cs",135,if(D3="auc",35,if(D3="","",if(C3="1"+0,if(C3="2"+20,if(C3="3"+40,if(C3="4"+60)) )))) )))))

It's not adding the extra value from cell C3......

What am I doing wrong?


 
to add to the value in C3 your if statement should be like below...

IF(C3="3",C3+40,what to do when false)

Value you want to add to.

After you provide the logical test, you must put a comma before you write what you want to do if it evaluates to true.

Hope that cleared it up for you.

P.S. I am pretty sure there is a better way to write your SUM formula instead of nesting so many IFs. If you provide a file with expected results I can possibly help you come up with a better formula.
 
Hi Excel Surgeon,

Thanks for the post. Basically I'm trying to create a formula that calculates total charge to client based on the item they've ordered, then adds the applicable locality surcharge based on their zone.

Sheet 1 contains the list of Suburbs I've allocated into zones:

PostcodeSuburbKms from CBDZoneSurcharge
4000Somewhereville0.84$60.00


Sheet 2 is the active sheet I use to track sales:

Client$214.50
StreetSuburbZoneItemSell (Inc)
1 Test StSomewhereville4cl$214.50


Column C (Zone) contains a formula that matches the suburb I manually enter in Column B to Column B in Sheet 1, returning the corresponding Zone from column D.
ie. =VLOOKUP(Sheet2!B3,Sheet1!$B$2:$D$600,3,0)

Column E contains my multiple If formula, which calculates the sell price based on the item code I've manually entered in Column D (Item)

What I need to do now, is find a way to calculate the total sell price in Column E, which will include the item price, plus the zone surcharge (ie. Zone 1 = $0, Zone 2 = $20, Zone 3 = $40, Zone 4 = $60)

Does this help?
 
Last edited by a moderator:
So the zone surcharge (column C) will be added to whatever is in column D?
 
Do this. Much simpler, plus it will be easier to change values later if you need to, or add more zones, etc.

In any two adjacent columns not being used put the values as such [we can hide them later to not show]... (for example I am using columns G and H)

GH
1CODEVALUE
2cl214.5
3cr77
4csd77
5cs135
6auc35
710
8220
9340
10460


After doing that in column E insert this formula: =IFERROR(VLOOKUP(D2,$G$2:$H$6,2,0)+VLOOKUP(C2,$G$7:$H$10,2,0),"you can write whatever here or leave blank")

SEE ATTACHED excel file and image. Make sure that is the behavior you wanted. Also if you are using columns other than the G or H I used make sure you adjust the formula accordingly.

attachment.php
 

Attachments

  • no_more.PNG
    no_more.PNG
    16.6 KB · Views: 40
  • no_more_long_formula.xlsx
    12.1 KB · Views: 7
Thanks Excel Surgeon, that worked a treat!

Last thing now - how can I return a blank cell instead of #N/A from the below formula if I have dragged the formula down but not yet entered a value in column T?

=VLOOKUP(SEP16!T3,Locality!$B$2:$D$600,3,0)



 
Last edited by a moderator:
@tigerfish
Hello,
please do not quote entire posts unnecessarily. They are just clutter and make the thread hard to read.Thx
 
I think Pecoflyer meant to say you can use =IFERROR(your_function,"").

Both =IFERROR(VLOOKUP(SEP16!T3,Locality!$B$2:$D$600,3,0),"") and =IF(SEP16!T3="","",VLOOKUP(SEP16!T3,Locality!$B$2:$D$600,3,0)) will do what you need it to do.

However, there is one difference I thought I should mention. If T3 has a value that is not in the lookup range the IFERROR will return a result of blank, while the IF(T3="","" method will return an #N/A. You can use either or depending on the result you want in case the value you are looking up is not in the list.
 
You must log in or register to reply here.
Back
Top