Conditional Formatting based on 2 sets of criteria?

[Ian1976]

Hi,

I'm using this formula...

=IFERROR(VLOOKUP(AG3,A2:T89,200)," ")

Is there a way to also check if the text in AG3 starts with 319 and the result returned from A2:T89 was >499 then colour the result if conditons are met in red?

Thanks

 

[ExcelElliott]

Try this for conditional format formula:

=AND(LEFT(AG3,3)="319",IFERROR(VLOOKUP(AG3,A2:T89,200),0)>499)

 

[Fluff]

Another way
Select T2:T89 and use this formula
=AND(LEFT(AG$3,3)="319",A2=AG$3,T2>499)

 

[Ian1976]

Thanks guys, also that should have read 20,0),” “) bloody keyboard playing up! ?

 

[jtakw]

Thanks guys, also that should have read 20,0),” “) bloody keyboard playing up! ��

Hi,

Also, shouldn't that be a "" (BLANK), rather than a " " (SPACE)?

=IFERROR(VLOOKUP(AG3,A2:T89,20,0),"")

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Ian1976]

No thank you guys! I love the knowledge on here and I love playing with my spreadsheets at work to make them more efficient and user friendly!

Jtakw - well spotted!

 

[Ian1976]

Morning guys,

I'm using this conditional formula,

=AND(LEFT(Z3,3)="319",IFERROR(VLOOKUP(Z3,M2:XEX89,20,0),""))

and it's filling in the cell with a colour, how do I apply this to a range, Z3:AU12, as it only seems to be checking AG3 cell info?

could you explain how the formula works so I can also adjust it to highlight results <499

Thanks

 

[Ian1976]

Sorry that was a rubbish post and I cant see how to edit it! So I'll try again...

I have a table Z19:AU28 Which is populated with this formula, =IFERROR(VLOOKUP(Z3,A2:T89,20,0)," ") I have a conditional format set up to detect results that are less than -800 but want another conditional formula to check another table Z3:AU12 for cells that start with 319 and are less than -400!

Hope that makes sense and if possible can the process be explained?

TIA
Ian

 

[Ian1976]

Right I have a working formula that checks Cell AG3 and highlights my required cell, all I need to know now is how to apply this formula (conditional) to the range Z19:AU28?

=AND(LEFT(AG3,3)="319",IFERROR(VLOOKUP(AG3,A2:T89,20,0),0)<=-300)

Thanks