|
|
|
|
could you send me your worksheet at
remove NOSPAM from address.
Mangesh
"Stephen" wrote in message
...
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.
|