# Lost with nested ifs and vlookup and or statement

#### tassie

##### Board Regular
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

##### MrExcel MVP
tassie said:
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

##### Board Regular

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,

##### MrExcel MVP
tassie said:
...

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

##### Board Regular

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

##### MrExcel MVP
tassie said:

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

##### Board Regular

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

Thanks

Tassie

Replies
0
Views
369
Replies
0
Views
375
Replies
14
Views
659
Replies
11
Views
665
Replies
3
Views
629

1,196,014
Messages
6,012,860
Members
441,737
Latest member
bijayche

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back