Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup Formula - but have a formula if it can't find/match a value
I'm trying to create a formula that applies different factors and then
promotional factors to a base rate. This was working perfectly with IF functions until I had more than seven possible promotions. My previous formula was: =IF((B14="BOGOF"),($A$15*B12)*$Q$4,IF((B14="PRICE" ),($A$15*B12)*$Q$5,($A$15*B12))) (So my promotions are called BOGOF, PRICE etc. (there are now 14 different options) and have different factors to multiply the base rate ($A$15) and then regardless are always multiplied by the seasonal factor (B12). What I don't understand, now needing to change to some kind of LOOKUP, is what happens if there is nothing the promotional line - every example I can find seems to enter a blank box or N/A or FALSE whereas I need it to fall back to the end of the above formula if there is no promotion on: ($A$15*AB12) I tried combining IF and ISTEXT but couldn't get them to nest. Any help would be very gratefully received! Thank you. |
#2
|
|||
|
|||
you could use:
=IF(ISNA(match(lookup_value,lookup_range,0)),your_ base_formula,VLOOKUP) Mangesh "Stephen" wrote in message ... I'm trying to create a formula that applies different factors and then promotional factors to a base rate. This was working perfectly with IF functions until I had more than seven possible promotions. My previous formula was: =IF((B14="BOGOF"),($A$15*B12)*$Q$4,IF((B14="PRICE" ),($A$15*B12)*$Q$5,($A$15* B12))) (So my promotions are called BOGOF, PRICE etc. (there are now 14 different options) and have different factors to multiply the base rate ($A$15) and then regardless are always multiplied by the seasonal factor (B12). What I don't understand, now needing to change to some kind of LOOKUP, is what happens if there is nothing the promotional line - every example I can find seems to enter a blank box or N/A or FALSE whereas I need it to fall back to the end of the above formula if there is no promotion on: ($A$15*AB12) I tried combining IF and ISTEXT but couldn't get them to nest. Any help would be very gratefully received! Thank you. |
#3
|
|||
|
|||
Thank you very much - i'm no excel pro so could you help me put that into an
example please? I see that look up range for example could be $B$5:$C$13, but what woudl go in the lookup_value place? Woudl that be the place where the promo code BOGOF may ro may not be? =IF(ISNA(match(B15,$B$5:$C$13,0)),($A$15*AB12),VLO OKUP) ? "Mangesh Yadav" wrote: you could use: =IF(ISNA(match(lookup_value,lookup_range,0)),your_ base_formula,VLOOKUP) Mangesh "Stephen" wrote in message ... I'm trying to create a formula that applies different factors and then promotional factors to a base rate. This was working perfectly with IF functions until I had more than seven possible promotions. My previous formula was: =IF((B14="BOGOF"),($A$15*B12)*$Q$4,IF((B14="PRICE" ),($A$15*B12)*$Q$5,($A$15* B12))) (So my promotions are called BOGOF, PRICE etc. (there are now 14 different options) and have different factors to multiply the base rate ($A$15) and then regardless are always multiplied by the seasonal factor (B12). What I don't understand, now needing to change to some kind of LOOKUP, is what happens if there is nothing the promotional line - every example I can find seems to enter a blank box or N/A or FALSE whereas I need it to fall back to the end of the above formula if there is no promotion on: ($A$15*AB12) I tried combining IF and ISTEXT but couldn't get them to nest. Any help would be very gratefully received! Thank you. |
#4
|
|||
|
|||
Hi Stephen,
you will have to use the formula: =$A$15*$B$12*IF(ISNA(MATCH(A1,$B$5:$C$13,0)),1,VLO OKUP(A1,$B$5:$C$13,2)) where A1 contains the BOGOF, or PRICE. And your table is B5:C13 Your table looks like: BOGOF value_from_cell_Q4 PRICE value_from_cell_Q5 and so on. But if you want to use the table as follows: BOGOF Q4 PRICE Q5 and so on. where Q4 is text as shown above, then use the formula as follows: =$A$15*$B$12*IF(ISNA(MATCH(A1,$B$5:$C$13,0)),1,IND IRECT(VLOOKUP(A1,$B$5:$C$1 3,2))) Mangesh "Stephen" wrote in message ... Thank you very much - i'm no excel pro so could you help me put that into an example please? I see that look up range for example could be $B$5:$C$13, but what woudl go in the lookup_value place? Woudl that be the place where the promo code BOGOF may ro may not be? =IF(ISNA(match(B15,$B$5:$C$13,0)),($A$15*AB12),VLO OKUP) ? "Mangesh Yadav" wrote: you could use: =IF(ISNA(match(lookup_value,lookup_range,0)),your_ base_formula,VLOOKUP) Mangesh "Stephen" wrote in message ... I'm trying to create a formula that applies different factors and then promotional factors to a base rate. This was working perfectly with IF functions until I had more than seven possible promotions. My previous formula was: =IF((B14="BOGOF"),($A$15*B12)*$Q$4,IF((B14="PRICE" ),($A$15*B12)*$Q$5,($A$15* B12))) (So my promotions are called BOGOF, PRICE etc. (there are now 14 different options) and have different factors to multiply the base rate ($A$15) and then regardless are always multiplied by the seasonal factor (B12). What I don't understand, now needing to change to some kind of LOOKUP, is what happens if there is nothing the promotional line - every example I can find seems to enter a blank box or N/A or FALSE whereas I need it to fall back to the end of the above formula if there is no promotion on: ($A$15*AB12) I tried combining IF and ISTEXT but couldn't get them to nest. Any help would be very gratefully received! Thank you. |
#5
|
|||
|
|||
Firstly thank you very much - you are being incrediby helpful and I really,
really apprecaite it. I have got the promos in a table on another workshhet (called Promos) that goes: Code Tesco JS Asda Morrisons Somerfield Waitrose Co-Op BOGOF 7 6 7 6 6 5 6 BOGOFGE THIRD 2FOR 1.5 1.5 1.5 1.5 1.5 1.5 1.5 3FOR HALF EDLP PRICE MISC So I have edited your fomula to read: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promos!C5:C13,2))) Excel accepts that - no errors, but when I go ahead and type BOGOF in to B14 it doesn't change the amount calculated in B15 (where the fomula is palced) Have I gone wrong somewhere? The cusotmer name on the worksheet is in $A$14 if that is relevant. "Mangesh Yadav" wrote: Hi Stephen, you will have to use the formula: =$A$15*$B$12*IF(ISNA(MATCH(A1,$B$5:$C$13,0)),1,VLO OKUP(A1,$B$5:$C$13,2)) where A1 contains the BOGOF, or PRICE. And your table is B5:C13 Your table looks like: BOGOF value_from_cell_Q4 PRICE value_from_cell_Q5 and so on. But if you want to use the table as follows: BOGOF Q4 PRICE Q5 and so on. where Q4 is text as shown above, then use the formula as follows: =$A$15*$B$12*IF(ISNA(MATCH(A1,$B$5:$C$13,0)),1,IND IRECT(VLOOKUP(A1,$B$5:$C$1 3,2))) Mangesh "Stephen" wrote in message ... Thank you very much - i'm no excel pro so could you help me put that into an example please? I see that look up range for example could be $B$5:$C$13, but what woudl go in the lookup_value place? Woudl that be the place where the promo code BOGOF may ro may not be? =IF(ISNA(match(B15,$B$5:$C$13,0)),($A$15*AB12),VLO OKUP) ? "Mangesh Yadav" wrote: you could use: =IF(ISNA(match(lookup_value,lookup_range,0)),your_ base_formula,VLOOKUP) Mangesh "Stephen" wrote in message ... I'm trying to create a formula that applies different factors and then promotional factors to a base rate. This was working perfectly with IF functions until I had more than seven possible promotions. My previous formula was: =IF((B14="BOGOF"),($A$15*B12)*$Q$4,IF((B14="PRICE" ),($A$15*B12)*$Q$5,($A$15* B12))) (So my promotions are called BOGOF, PRICE etc. (there are now 14 different options) and have different factors to multiply the base rate ($A$15) and then regardless are always multiplied by the seasonal factor (B12). What I don't understand, now needing to change to some kind of LOOKUP, is what happens if there is nothing the promotional line - every example I can find seems to enter a blank box or N/A or FALSE whereas I need it to fall back to the end of the above formula if there is no promotion on: ($A$15*AB12) I tried combining IF and ISTEXT but couldn't get them to nest. Any help would be very gratefully received! Thank you. |
#6
|
|||
|
|||
Hi Stephen,
first let me explain to you what the formula is trying to do: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) The first part is =A15*B12 The second part is IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1,VLOOKUP(B14, Promos!C5:C13,2)) which is multiplied to the first part Now as you have said that the first part is common, we have to multiply it with 1 for "value not found in table", and with "some" value when it is found in the table. So what the formula does is: MATCH(B14,Promos!B5:B13,0) The range B5:B13 will hold the lookup values in which you are searching the value you enter in B14, so probably this will have the codes: BOGOF BOGOFGE THIRD 2FOR 3FOR HALF EDLP PRICE MISC The match functions returns the position of the lookup value so if you are looking for PRICE, then it returns 8, and if not found, it returns #N/A. So I check the result with ISNA. If #N/A found then multiply with 1 (which is the first part of the if statement). Now if price is found, then I need to do a VLOOKUP VLOOKUP(B14,Promos!B5:C13,2) Here, the vlookup will lookup for PRICE (which is given in B14), in the range B5:B13, and if found will return the value from the range C5:C13 (which is specified by 2 - the second column in the formula above) Note that it is not a typo, this should have B5:C13. Now the example you gave, which value from this table should be multiplied to the baformula. Mangesh so when lookup does not find the given value from B14 then the first part is multiplied by 1 which is in the formula, but if "Stephen" wrote in message ... Firstly thank you very much - you are being incrediby helpful and I really, really apprecaite it. I have got the promos in a table on another workshhet (called Promos) that goes: Code Tesco JS Asda Morrisons Somerfield Waitrose Co-Op BOGOF 7 6 7 6 6 5 6 BOGOFGE THIRD 2FOR 1.5 1.5 1.5 1.5 1.5 1.5 1.5 3FOR HALF EDLP PRICE MISC So I have edited your fomula to read: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) Excel accepts that - no errors, but when I go ahead and type BOGOF in to B14 it doesn't change the amount calculated in B15 (where the fomula is palced) Have I gone wrong somewhere? The cusotmer name on the worksheet is in $A$14 if that is relevant. |
#7
|
|||
|
|||
So if I correct it to:
=$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:C13,0)),1, INDIRECT(VLOOKUP(B14,Promos!B5:C13,2))) When I put BOGOF in B14 it should multiply by 7 (the value in Promos!C5) but at the moment it ignores it and just does the $A$15*B$12 calculation (which it does correctly). Thank you again and I'm so sorry for begin so slow to pick this up. "Mangesh Yadav" wrote: Hi Stephen, first let me explain to you what the formula is trying to do: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) The first part is =A15*B12 The second part is IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1,VLOOKUP(B14, Promos!C5:C13,2)) which is multiplied to the first part Now as you have said that the first part is common, we have to multiply it with 1 for "value not found in table", and with "some" value when it is found in the table. So what the formula does is: MATCH(B14,Promos!B5:B13,0) The range B5:B13 will hold the lookup values in which you are searching the value you enter in B14, so probably this will have the codes: BOGOF BOGOFGE THIRD 2FOR 3FOR HALF EDLP PRICE MISC The match functions returns the position of the lookup value so if you are looking for PRICE, then it returns 8, and if not found, it returns #N/A. So I check the result with ISNA. If #N/A found then multiply with 1 (which is the first part of the if statement). Now if price is found, then I need to do a VLOOKUP VLOOKUP(B14,Promos!B5:C13,2) Here, the vlookup will lookup for PRICE (which is given in B14), in the range B5:B13, and if found will return the value from the range C5:C13 (which is specified by 2 - the second column in the formula above) Note that it is not a typo, this should have B5:C13. Now the example you gave, which value from this table should be multiplied to the baformula. Mangesh so when lookup does not find the given value from B14 then the first part is multiplied by 1 which is in the formula, but if "Stephen" wrote in message ... Firstly thank you very much - you are being incrediby helpful and I really, really apprecaite it. I have got the promos in a table on another workshhet (called Promos) that goes: Code Tesco JS Asda Morrisons Somerfield Waitrose Co-Op BOGOF 7 6 7 6 6 5 6 BOGOFGE THIRD 2FOR 1.5 1.5 1.5 1.5 1.5 1.5 1.5 3FOR HALF EDLP PRICE MISC So I have edited your fomula to read: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) Excel accepts that - no errors, but when I go ahead and type BOGOF in to B14 it doesn't change the amount calculated in B15 (where the fomula is palced) Have I gone wrong somewhere? The cusotmer name on the worksheet is in $A$14 if that is relevant. |
#8
|
|||
|
|||
Hi Stephen,
do not use the INDIRECT. It was for another case that I had given you. Use the formula as follows: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:C13,0)),1, VLOOKUP(B14,Promos!B5:C13, 2)) Mangesh "Stephen" wrote in message ... So if I correct it to: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:C13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!B5:C13,2))) When I put BOGOF in B14 it should multiply by 7 (the value in Promos!C5) but at the moment it ignores it and just does the $A$15*B$12 calculation (which it does correctly). Thank you again and I'm so sorry for begin so slow to pick this up. "Mangesh Yadav" wrote: Hi Stephen, first let me explain to you what the formula is trying to do: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) The first part is =A15*B12 The second part is IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1,VLOOKUP(B14, Promos!C5:C13,2)) which is multiplied to the first part Now as you have said that the first part is common, we have to multiply it with 1 for "value not found in table", and with "some" value when it is found in the table. So what the formula does is: MATCH(B14,Promos!B5:B13,0) The range B5:B13 will hold the lookup values in which you are searching the value you enter in B14, so probably this will have the codes: BOGOF BOGOFGE THIRD 2FOR 3FOR HALF EDLP PRICE MISC The match functions returns the position of the lookup value so if you are looking for PRICE, then it returns 8, and if not found, it returns #N/A. So I check the result with ISNA. If #N/A found then multiply with 1 (which is the first part of the if statement). Now if price is found, then I need to do a VLOOKUP VLOOKUP(B14,Promos!B5:C13,2) Here, the vlookup will lookup for PRICE (which is given in B14), in the range B5:B13, and if found will return the value from the range C5:C13 (which is specified by 2 - the second column in the formula above) Note that it is not a typo, this should have B5:C13. Now the example you gave, which value from this table should be multiplied to the baformula. Mangesh so when lookup does not find the given value from B14 then the first part is multiplied by 1 which is in the formula, but if "Stephen" wrote in message ... Firstly thank you very much - you are being incrediby helpful and I really, really apprecaite it. I have got the promos in a table on another workshhet (called Promos) that goes: Code Tesco JS Asda Morrisons Somerfield Waitrose Co-Op BOGOF 7 6 7 6 6 5 6 BOGOFGE THIRD 2FOR 1.5 1.5 1.5 1.5 1.5 1.5 1.5 3FOR HALF EDLP PRICE MISC So I have edited your fomula to read: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) Excel accepts that - no errors, but when I go ahead and type BOGOF in to B14 it doesn't change the amount calculated in B15 (where the fomula is palced) Have I gone wrong somewhere? The cusotmer name on the worksheet is in $A$14 if that is relevant. |
#9
|
|||
|
|||
I have changed the formula to:
=$A$15*B$12*IF(ISNA(MATCH(B$14,Promos!$B$5:$C$13,0 )),1,VLOOKUP(B$14,Promos!$B$5:$C$13,2)) But for some reason it still ignores whether or not I type a matching BOGOf or other code into B14 and only does the $A$15*B$12 calculation. "Mangesh Yadav" wrote: Hi Stephen, do not use the INDIRECT. It was for another case that I had given you. Use the formula as follows: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:C13,0)),1, VLOOKUP(B14,Promos!B5:C13, 2)) Mangesh "Stephen" wrote in message ... So if I correct it to: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:C13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!B5:C13,2))) When I put BOGOF in B14 it should multiply by 7 (the value in Promos!C5) but at the moment it ignores it and just does the $A$15*B$12 calculation (which it does correctly). Thank you again and I'm so sorry for begin so slow to pick this up. "Mangesh Yadav" wrote: Hi Stephen, first let me explain to you what the formula is trying to do: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) The first part is =A15*B12 The second part is IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1,VLOOKUP(B14, Promos!C5:C13,2)) which is multiplied to the first part Now as you have said that the first part is common, we have to multiply it with 1 for "value not found in table", and with "some" value when it is found in the table. So what the formula does is: MATCH(B14,Promos!B5:B13,0) The range B5:B13 will hold the lookup values in which you are searching the value you enter in B14, so probably this will have the codes: BOGOF BOGOFGE THIRD 2FOR 3FOR HALF EDLP PRICE MISC The match functions returns the position of the lookup value so if you are looking for PRICE, then it returns 8, and if not found, it returns #N/A. So I check the result with ISNA. If #N/A found then multiply with 1 (which is the first part of the if statement). Now if price is found, then I need to do a VLOOKUP VLOOKUP(B14,Promos!B5:C13,2) Here, the vlookup will lookup for PRICE (which is given in B14), in the range B5:B13, and if found will return the value from the range C5:C13 (which is specified by 2 - the second column in the formula above) Note that it is not a typo, this should have B5:C13. Now the example you gave, which value from this table should be multiplied to the baformula. Mangesh so when lookup does not find the given value from B14 then the first part is multiplied by 1 which is in the formula, but if "Stephen" wrote in message ... Firstly thank you very much - you are being incrediby helpful and I really, really apprecaite it. I have got the promos in a table on another workshhet (called Promos) that goes: Code Tesco JS Asda Morrisons Somerfield Waitrose Co-Op BOGOF 7 6 7 6 6 5 6 BOGOFGE THIRD 2FOR 1.5 1.5 1.5 1.5 1.5 1.5 1.5 3FOR HALF EDLP PRICE MISC So I have edited your fomula to read: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) Excel accepts that - no errors, but when I go ahead and type BOGOF in to B14 it doesn't change the amount calculated in B15 (where the fomula is palced) Have I gone wrong somewhere? The cusotmer name on the worksheet is in $A$14 if that is relevant. |
#10
|
|||
|
|||
Try this formula. Simply copy it in your sheet as it is without any changes:
=$A$15*B$12*IF(ISNA(MATCH(B$14,Promos!$B$5:$B$13,0 )),1,VLOOKUP(B$14,Promos!$ B$5:$C$13,2)) Note: In match use B5:B13 (1 column only) In vlookup use B5:C13 (2 columns here) Mangesh "Stephen" wrote in message ... I have changed the formula to: =$A$15*B$12*IF(ISNA(MATCH(B$14,Promos!$B$5:$C$13,0 )),1,VLOOKUP(B$14,Promos!$ B$5:$C$13,2)) But for some reason it still ignores whether or not I type a matching BOGOf or other code into B14 and only does the $A$15*B$12 calculation. "Mangesh Yadav" wrote: Hi Stephen, do not use the INDIRECT. It was for another case that I had given you. Use the formula as follows: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:C13,0)),1, VLOOKUP(B14,Promos!B5:C13, 2)) Mangesh "Stephen" wrote in message ... So if I correct it to: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:C13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!B5:C13,2))) When I put BOGOF in B14 it should multiply by 7 (the value in Promos!C5) but at the moment it ignores it and just does the $A$15*B$12 calculation (which it does correctly). Thank you again and I'm so sorry for begin so slow to pick this up. "Mangesh Yadav" wrote: Hi Stephen, first let me explain to you what the formula is trying to do: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) The first part is =A15*B12 The second part is IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1,VLOOKUP(B14, Promos!C5:C13,2)) which is multiplied to the first part Now as you have said that the first part is common, we have to multiply it with 1 for "value not found in table", and with "some" value when it is found in the table. So what the formula does is: MATCH(B14,Promos!B5:B13,0) The range B5:B13 will hold the lookup values in which you are searching the value you enter in B14, so probably this will have the codes: BOGOF BOGOFGE THIRD 2FOR 3FOR HALF EDLP PRICE MISC The match functions returns the position of the lookup value so if you are looking for PRICE, then it returns 8, and if not found, it returns #N/A. So I check the result with ISNA. If #N/A found then multiply with 1 (which is the first part of the if statement). Now if price is found, then I need to do a VLOOKUP VLOOKUP(B14,Promos!B5:C13,2) Here, the vlookup will lookup for PRICE (which is given in B14), in the range B5:B13, and if found will return the value from the range C5:C13 (which is specified by 2 - the second column in the formula above) Note that it is not a typo, this should have B5:C13. Now the example you gave, which value from this table should be multiplied to the baformula. Mangesh so when lookup does not find the given value from B14 then the first part is multiplied by 1 which is in the formula, but if "Stephen" wrote in message ... Firstly thank you very much - you are being incrediby helpful and I really, really apprecaite it. I have got the promos in a table on another workshhet (called Promos) that goes: Code Tesco JS Asda Morrisons Somerfield Waitrose Co-Op BOGOF 7 6 7 6 6 5 6 BOGOFGE THIRD 2FOR 1.5 1.5 1.5 1.5 1.5 1.5 1.5 3FOR HALF EDLP PRICE MISC So I have edited your fomula to read: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) Excel accepts that - no errors, but when I go ahead and type BOGOF in to B14 it doesn't change the amount calculated in B15 (where the fomula is palced) Have I gone wrong somewhere? The cusotmer name on the worksheet is in $A$14 if that is relevant. |
#11
|
|||
|
|||
Right, tried that as:
=$A$15*B$12*IF(ISNA(MATCH(B$14,Promos!$B$5:$B$13,0 )),1,VLOOKUP(B$14,Promos!$B$5:$C$13,3)) If I put BOGOF in B14 it makes the result 0 and if I put 2FOR in, it results: #N/A BOGOF (!PromosB5 and the figure 7 in C5) and 2FOR (!PromosB6 and the figure 0.5 in C6). "Mangesh Yadav" wrote: Try this formula. Simply copy it in your sheet as it is without any changes: =$A$15*B$12*IF(ISNA(MATCH(B$14,Promos!$B$5:$B$13,0 )),1,VLOOKUP(B$14,Promos!$ B$5:$C$13,2)) Note: In match use B5:B13 (1 column only) In vlookup use B5:C13 (2 columns here) Mangesh "Stephen" wrote in message ... I have changed the formula to: =$A$15*B$12*IF(ISNA(MATCH(B$14,Promos!$B$5:$C$13,0 )),1,VLOOKUP(B$14,Promos!$ B$5:$C$13,2)) But for some reason it still ignores whether or not I type a matching BOGOf or other code into B14 and only does the $A$15*B$12 calculation. "Mangesh Yadav" wrote: Hi Stephen, do not use the INDIRECT. It was for another case that I had given you. Use the formula as follows: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:C13,0)),1, VLOOKUP(B14,Promos!B5:C13, 2)) Mangesh "Stephen" wrote in message ... So if I correct it to: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:C13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!B5:C13,2))) When I put BOGOF in B14 it should multiply by 7 (the value in Promos!C5) but at the moment it ignores it and just does the $A$15*B$12 calculation (which it does correctly). Thank you again and I'm so sorry for begin so slow to pick this up. "Mangesh Yadav" wrote: Hi Stephen, first let me explain to you what the formula is trying to do: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) The first part is =A15*B12 The second part is IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1,VLOOKUP(B14, Promos!C5:C13,2)) which is multiplied to the first part Now as you have said that the first part is common, we have to multiply it with 1 for "value not found in table", and with "some" value when it is found in the table. So what the formula does is: MATCH(B14,Promos!B5:B13,0) The range B5:B13 will hold the lookup values in which you are searching the value you enter in B14, so probably this will have the codes: BOGOF BOGOFGE THIRD 2FOR 3FOR HALF EDLP PRICE MISC The match functions returns the position of the lookup value so if you are looking for PRICE, then it returns 8, and if not found, it returns #N/A. So I check the result with ISNA. If #N/A found then multiply with 1 (which is the first part of the if statement). Now if price is found, then I need to do a VLOOKUP VLOOKUP(B14,Promos!B5:C13,2) Here, the vlookup will lookup for PRICE (which is given in B14), in the range B5:B13, and if found will return the value from the range C5:C13 (which is specified by 2 - the second column in the formula above) Note that it is not a typo, this should have B5:C13. Now the example you gave, which value from this table should be multiplied to the baformula. Mangesh so when lookup does not find the given value from B14 then the first part is multiplied by 1 which is in the formula, but if "Stephen" wrote in message ... Firstly thank you very much - you are being incrediby helpful and I really, really apprecaite it. I have got the promos in a table on another workshhet (called Promos) that goes: Code Tesco JS Asda Morrisons Somerfield Waitrose Co-Op BOGOF 7 6 7 6 6 5 6 BOGOFGE THIRD 2FOR 1.5 1.5 1.5 1.5 1.5 1.5 1.5 3FOR HALF EDLP PRICE MISC So I have edited your fomula to read: =$A$15*B$12*IF(ISNA(MATCH(B14,Promos!B5:B13,0)),1, INDIRECT(VLOOKUP(B14,Promo s!C5:C13,2))) Excel accepts that - no errors, but when I go ahead and type BOGOF in to B14 it doesn't change the amount calculated in B15 (where the fomula is palced) Have I gone wrong somewhere? The cusotmer name on the worksheet is in $A$14 if that is relevant. |
#12
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify formula | Excel Worksheet Functions | |||
Lookup formula | Excel Discussion (Misc queries) | |||
Can't find the right lookup formula for this | Excel Worksheet Functions | |||
Formula Question... LookUP | Excel Discussion (Misc queries) | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions |