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.