Can you put an IF AND and OR in one formula with vlookup?

[AStereotypicalGamer]

So currently I have multiple columns of functions in order to pull data over from another sheet. I am looking for a way to spin it a different way and also cut down on the # of columns i have in my current sheet.


Takes entry from Column A and finds it on VIP31 sheet. If the cooresponding value in Column H on the VIP31 sheet is Security it does the math if not returns FALSE.

=IF(VLOOKUP($A5:$A5,'VIP31'!$A:$H,8,FALSE)="Enterprise Networks",(($F5*(VLOOKUP($A5,'VIP31'!$A:$B,2,0)%)*1%)))

Takes entry from Column A and finds on the VIP31 sheet. If column E is Category D or Category E it does the math, otherwise returns False.

=IF(OR(VLOOKUP($A4:$A4,'VIP31'!$A:$E,5,FALSE)="Category D",VLOOKUP($A4:$A4,'VIP31'!$A:$E,5,FALSE)="Category E"),(($F4*(VLOOKUP($A4,'VIP31'!$A:$B,2,0)%)*1%)))

Is there a way to make one that will take the entry from Column A, vlook it up and see if Category D (Column E on "VIP31") AND if it is "Enterprise Networks" OR "Meraki" (Column H on "VIP31") Something like the below (i know its not right - otherwise i wouldn't be asking haha )

=IF(AND((VLOOKUP($A4:$A4,'VIP31'!$A:$E,5,FALSE)="Category  D")OR(VLOOKUP($A5:$A5,'VIP31'!$A:$H,8,FALSE)="Enterprise Networks",(VLOOKUP($A5:$A5,'VIP31'!$A:$H,8,FALSE)="Meraki",(($F4*(VLOOKUP($A4,'VIP31'!$A:$B,2,0)%)*1%)))

thanks,

 

[Special-K99]

You can only lookup ONE value, VLOOKUP(A4, not VLOOKUP(A4:A4...
Limiting your ranges will speed things up A1:E1000 not A:E

AND() and OR() list the conditions separarted by commas, e.g. OR(A1=1,A1=2,A1=3, etc...)


Try

=IF(AND(VLOOKUP(A4,'VIP31'!A1:E1000,5,FALSE)="Category D",OR(VLOOKUP(A4,'VIP31'!A1:H1000,8,FALSE)="Enterprise Networks",VLOOKUP(A4,'VIP31'!A1:H1000,8,FALSE)="Meraki")),F4*VLOOKUP(A4,'VIP31'!A1:B1000,2,0)%*1%)

 

[AStereotypicalGamer]

Yeah I had A:E because the second sheet it looks at is ever changing based on the month and fluctuates between 15,000-25,000 rows.

Trying that seems to work once i changed the 1000s to 20000s to cover the row count. Awesome - thank you!