Some help with conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2721793%22%20slang%3D%22en-US%22%3ESome%20help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2721793%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20folks.%20Here's%20the%20scenario%20I%20have.%20I%20have%20two%20sheets%20open%3B%20in%20sheet2%20I%20would%20like%20cell%20A1%20to%20change%20colour%20if%20any%20cell%20in%20a%20range%20in%20sheet1%20contains%20a%20certain%20value%2C%20eg.%20cat.%20I%20had%20done%20this%20in%20Google%20Sheets%20using%20the%20following%20formula%3A%26nbsp%3B%3Dnot(iserror(vlookup(%22cat%22%2Cindirect(%22sheet2!A1%3AA20%22)%2C1%2Cfalse)))%20But%20it%20doesn't%20seem%20to%20work%20in%20Excel%20and%20I%20can't%20quite%20figure%20out%20why%20this%20formula%20results%20in%20a%20false%20output.%20I%20have%20verified%20that%20the%20range%20does%20contain%20the%20value%20it's%20looking%20for.%20Any%20suggestions%20are%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2721793%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2721816%22%20slang%3D%22en-US%22%3ERe%3A%20Some%20help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2721816%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F865945%22%20target%3D%22_blank%22%3E%40MrVmaths%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DMATCH(A1%3Bsheet2!A1%3AA20%3B0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIf%20the%20value%20you%20are%20looking%20for%20is%20on%20A1%20youc%20an%20use%20this%20formula%20on%20conditional%20formatting%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2722815%22%20slang%3D%22en-US%22%3ERe%3A%20Some%20help%20with%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2722815%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20response.%20Unfortunately%2C%20the%20cell%20I%20want%20to%20be%20formatted%20doesn't%20contain%20the%20same%20thing%20that%20I%20want%20it%20to%20search%20for.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hey folks. Here's the scenario I have. I have two sheets open; in sheet2 I would like cell A1 to change colour if any cell in a range in sheet1 contains a certain value, eg. cat. I had done this in Google Sheets using the following formula: =not(iserror(vlookup("cat",indirect("sheet2!A1:A20"),1,false))) But it doesn't seem to work in Excel and I can't quite figure out why this formula results in a false output. I have verified that the range does contain the value it's looking for. Any suggestions are appreciated.

4 Replies

@MrVmaths 

 

If the value you are looking for is on A1 youc an use this formula on conditional formatting

=MATCH(A1,sheet2!A1:A20,0)

 

 

 

@Juliano-Petrukio Thanks for your response. Unfortunately, the cell I want to be formatted doesn't contain the same thing that I want it to search for.

@MrVmaths 

It doesn't matter.

You just need to update the range or cell where you want the formatting to be applied.

Thanks again. In your example, it assumes that the word Cat appears in cell A1 of Sheet2, so the formula is looking to see if a cell in the range contains the same value as A1.

In my case the word Cat will only appear in the range in Sheet1.