Lost with nested ifs and vlookup and or statement

[tassie]

Hi Guys

This formula work ok.
IF(ROUND(VLOOKUP(B16,prev,3,0)*1.1,2)<>E16,"C","")

The challenge I am having is how to go about including another
3 vlookups with an OR function statement.

If rounded vlookups ne E16 OR F17 OR F18 OR F19 then "C" else null

Appreciate some feedback

Regards,

Tassie

 

[Aladin Akyurek]

Hi Guys

This formula work ok.
IF(ROUND(VLOOKUP(B16,prev,3,0)*1.1,2)<>E16,"C","")

The challenge I am having is how to go about including another
3 vlookups with an OR function statement.

If rounded vlookups ne E16 OR F17 OR F18 OR F19 then "C" else null

Appreciate some feedback

Regards,

Tassie

What are those 3 Vlookup's and their relation with E16, F17:F19?

 

[tassie]

Hi Aladin,

BMC pricelist.xls
	A	B	C	D	E	F	G	H
10		SKU	Description	Code	L2	L5	L8	L9
11		1123397	BAR CHAIRS 25/40 (BAG 100)	SOG2540	16.99	16.49	15.99	15.49
12		1123410	BAR CHAIRS 50/65 (BAG 100)	SOG5065	17.84	17.32	16.79	16.27
13		1123436	BAR CHAIRS 75/90 (BAG 100)	SOG7590	20.39	19.79	19.19	18.59
14		1199282	BAR CHAIRS 85/100 (BAG 100)	SOG85/100	20.39	19.79	19.19	18.59
reps_list

From column A I would like to test if any of the four prices EG l2,l5,l8 or L9
do not equal the products previous 4 prices which I save in a named range
called PREV.

=IF(ROUND(VLOOKUP(B11,prev,3,0)*1.1,2)<>E11,"C","")
=IF(ROUND(VLOOKUP(B11,prev,4,0)*1.1,2)<>E12,"C","")
=IF(ROUND(VLOOKUP(B11,prev,5,0)*1.1,2)<>E13,"C","")
=IF(ROUND(VLOOKUP(B11,prev,6,0)*1.1,2)<>E14,"C","")

Hope this helps

Thanks for taking the time.
Tassie,

 

[Aladin Akyurek]

...

From column A I would like to test if any of the four prices EG l2,l5,l8 or L9
do not equal the products previous 4 prices which I save in a named range
called PREV.

=IF(ROUND(VLOOKUP(B11,prev,3,0)*1.1,2)<>E11,"C","")
=IF(ROUND(VLOOKUP(B11,prev,4,0)*1.1,2)<>E12,"C","")
=IF(ROUND(VLOOKUP(B11,prev,5,0)*1.1,2)<>E13,"C","")
=IF(ROUND(VLOOKUP(B11,prev,6,0)*1.1,2)<>E14,"C","")
...

Not sure but try:

=IF(SUM(ISNA(MATCH(VLOOKUP(B11,prev,{3,4,5,6},0)*1.1,2),E11:E14,0))+0),"C","")

which must be confirmed with control+shift+enter, not just with enter.

 

[tassie]

ADVERTISEMENT

Hi Aladin,

You certainly have me thinking outside my current capability with your suggestion.

It doesn't work... looking at seems a round bracket is missing and not sure where your logic needs it to be.

Question.

Is this replacing 4 vlookups and an OR function?
If so..**** powerful

And with the F2 ctrl+shift+enfer create an array?

Curious...

Tassie

 

[Aladin Akyurek]

Hi Aladin,

You certainly have me thinking outside my current capability with your suggestion.

It doesn't work... looking at seems a round bracket is missing and not sure where your logic needs it to be.

Forgot that bit...

=IF(SUM(ISNA(MATCH(ROUND(VLOOKUP(B11,prev,{3,4,5,6},0)*1.1,2),E11:E14,0))+0),"C","")

Question.

Is this replacing 4 vlookups and an OR function?
If so..**** powerful

Yes.

And with the F2 ctrl+shift+enfer create an array?

Curious...

Tassie

Just control+shift+enter instead of just enter.

 

[tassie]

Hi Aladin,

Works beautifully...
Very powerful still getting a handle on how the array works.
Defintely will be using more in the future

Appreciate your time

Thanks

Tassie