Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Stephen
 
Posts: n/a
Default 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   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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   Report Post  
Stephen
 
Posts: n/a
Default

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   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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   Report Post  
Stephen
 
Posts: n/a
Default

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   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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   Report Post  
Stephen
 
Posts: n/a
Default

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   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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   Report Post  
Stephen
 
Posts: n/a
Default

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   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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   Report Post  
Stephen
 
Posts: n/a
Default

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   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

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.











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Simplify formula Luke Excel Worksheet Functions 37 May 6th 05 07:21 AM
Lookup formula Esrei Excel Discussion (Misc queries) 1 April 1st 05 02:36 PM
Can't find the right lookup formula for this bankscl Excel Worksheet Functions 4 March 28th 05 06:27 PM
Formula Question... LookUP G Excel Discussion (Misc queries) 4 March 7th 05 02:38 AM
Relative Indirect Formula Referencing? Damian Excel Worksheet Functions 1 January 7th 05 05:16 AM


All times are GMT +1. The time now is 02:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"