0

I want to change the text color to Red for cells in range A3:A25 by comparing the corresponding values in the worksheet named 'Stock Master'. i.e., if the corresponding value (reference present in Column A of 'Stock Master') in Column L of 'Stock Master' is less than the value in Column M of 'Stock Master', change the text color to RED.

I used the following custom formula for conditional and I am getting the Error Message: "Invalid Formula"

=vlookup(A3,INDIRECT('Stock Master'!$A$3:$M$300,12, False))<vlookup(A3, INDIRECT('Stock Master'!$A$3:$M$300,13,False))

What is wrong with my custom formula for conditional formatting?

2
  • Maybe your locale expects semicolons instead of commas?
    – Martín
    Feb 10 at 12:11
  • Couldn't understand "locale" !! Feb 10 at 14:33

1 Answer 1

0

Syntax Error

INDIRECT takes a reference-as-string or a reference to a cell that contains a reference-as-string, but you are passing it a range reference.

You need to add quotation marks to convert the range reference to a reference-as-string.

Examples

  1. Reference-as-string:
    • "A:B"
    • "A1"
  2. Reference:
    • A:B - incorrect
    • A1 - correct only if A1 contains a reference-as-string.

Corrected Formula

=VLOOKUP(A3, INDIRECT("'Stock Master'!A3:M300"), 12, 0)<
 VLOOKUP(A3, INDIRECT("'Stock Master'!A3:M300"), 13, 0)

or using LET

=LET(r,INDIRECT("'Stock Master'!A3:M300"),
   VLOOKUP(A3, r, 12, 0)<VLOOKUP(A3, r, 13, 0))

Code Efficiency

  • I replaced FALSE with 0.
  • I removed the absolute symbols $ since they are meaningless in a text string.

Alternatives

=LEN(QUERY(
   INDIRECT("'Stock Master'!A3:M300"), 
   "WHERE Col1='"& A3 &"' AND Col12 < Col13"))

Alternative formula 1

=LET(r,INDIRECT("'Stock Master'!A3:M300"),
   SUMPRODUCT(A3=INDEX(r,,1),
     INDEX(r,,12)<INDEX(r,,13))) 

Alternative formula 2

 


Functions Used

INDEX  INDIRECT  LET  QUERY  SUMPRODUCT  VLOOKUP

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.