Comparison of Cells

[Mightywarrior]

Hi All,

Request your help in understanding in the below requirement and please let me know if there's a suitable formula for it to be executed:-
Would like to compare two columns and if the value in first column gets exceeded or equal to when compared with the second then it needs to be highlighted with a suitable colour in the corresponding third column.

For Example
Column 1 - Current Fiscal Value like 100000, 900, 40000
Column 2 - Sales Value like High, Medium, Low
Column 3 - Cities like New York, Bombay, Paris
Benchmark for "Sales Value" would be like the below and it would be constant
High 100000
Medium 10000
Low 1000

Thanks

 

[Special-K99]

Assume columns are A B and C.

Select C1

Conditional Formatting
New Rule
Use a formula to determine...

=(A1>=LOOKUP(B1,{"High","Low","Medium"},{100000,1000,10000})
NOTE: The LOOKUP values need to be in alphabetical order so the numeric values have been reordered to reflect this.
Enter the formula EXACTLY as it appears above, do NOT change the order of the LOOKUP values

Format as required
Use Format painter (paintbrush icon) to copy to other cells

 

[Mightywarrior]

@Special-k99 - First of all thanks for your speedy assistance on this. But when I apply the provided formula in conditional formatting, I am getting the below as an error
"You may not use reference operators ( such as unions, intersections and ranges) or array constants for conditional formatting criteria."

 

[Special-K99]

Sorry I should have tested this first.

Use this instead.

=(A1>=IF(B1="High",100000,IF(B1="Low",1000,IF(B1="Medium",10000))))

 

[Mightywarrior]

Thanks a lot, it worked exactly as i wanted it to be.