Lookup Formula - but have a formula if it can't find/match a value

[Guest]

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.

 

[Mangesh Yadav]

you could use:

=IF(ISNA(match(lookup_value,lookup_range,0)),your_base_formula,VLOOKUP)

Mangesh

 

[Guest]

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),VLOOKUP) ?

 

[Mangesh Yadav]

Hi Stephen,

you will have to use the formula:

=$A$15*$B$12*IF(ISNA(MATCH(A1,$B$5:$C$13,0)),1,VLOOKUP(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,INDIRECT(VLOOKUP(A1,$B$5:$C$1
3,2)))


Mangesh

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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]

could you send me your worksheet at (e-mail address removed)
remove NOSPAM from address.

Mangesh