SOLVED

Conditional Formatting not working with text

%3CLINGO-SUB%20id%3D%22lingo-sub-2741374%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20not%20working%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2741374%22%20slang%3D%22en-US%22%3E%3CP%3Eon%20one%20tab%20I%20have%20a%20list%20of%20names%3C%2FP%3E%3CP%3Eon%20a%20second%20tab%20I%20have%20a%20list%20of%20the%20names%20and%20next%20to%20them%20in%20another%20cell%20an%20A%2C%20B%2C%20or%20C%20ranking%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20the%20first%20tab%20to%20match%20the%20name%20in%20the%20second%20tab%20to%20the%20ranking%20next%20to%20it%20and%20color%20the%20cell%20based%20on%20the%20latter%20ranking.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%20if%20the%20names%20are%20numbers%2C%20but%20once%20it%20is%20actual%20text%20names%20it%20doesn't%20work%20properly.%20I%20am%20making%20this%20in%20a%20google%20sheets%20doc%20as%20I%20do%20not%20have%20365%20to%20do%20filter%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20this%20coding%3A%3C%2FP%3E%3CP%3E%3DVLOOKUP(E2%2Cindirect(%22Names!B2%3AC115%22)%2C2%2CTRUE)%20%3D%20%22A%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20pictures%20the%20rankings%20are%20the%20same.%20A(green)%20for%201-29%20B(Yellow)%2030-70%20C(Red)71-114.%20All%20names%20are%20from%20a%20name%20generator%20and%20not%20actual%20names.%20Just%20trying%20to%20make%20the%20function%20work%20properly.%20%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22names-x.png%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309676iD7A0B88BD0BD9FC9%2Fimage-size%2Fsmall%3Fv%3Dv2%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22names-x.png%22%20alt%3D%22names-x.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22numbers-y.png%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309677i531DA064EC1E3C25%2Fimage-size%2Fsmall%3Fv%3Dv2%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22numbers-y.png%22%20alt%3D%22numbers-y.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EDan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2741374%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2741666%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20not%20working%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2741666%22%20slang%3D%22en-US%22%3ESince%20you've%20created%20a%20spreadsheet%20with%20false%20names%2C%20why%20don't%20you%20post%20it%2C%20rather%20than%20just%20posting%20an%20image.%20That%20way%20you%20wouldn't%20be%20forcing%20us%20to%20create%20our%20own%20copy....we%20could%20work%20with%20your%20anonymous%20data.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2742162%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20not%20working%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2742162%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1152606%22%20target%3D%22_blank%22%3E%40Danimal513%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20working%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20236px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309717i061199F0D1CAFF1C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EConditional%20formatting%20rule%20formulae%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(%24H%242%3A%24H%245%2CMATCH(E2%2C%24G%242%3A%24G%245%2C0))%20%3D%20%22A%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

on one tab I have a list of names

on a second tab I have a list of the names and next to them in another cell an A, B, or C ranking

 

I want the first tab to match the name in the second tab to the ranking next to it and color the cell based on the latter ranking.

 

This works if the names are numbers, but once it is actual text names it doesn't work properly. I am making this in a google sheets doc as I do not have 365 to do filter formulas.

 

Using this coding:

=VLOOKUP(E2,indirect("Names!B2:C115"),2,TRUE) = "A"

 

In the pictures the rankings are the same. A(green) for 1-29 B(Yellow) 30-70 C(Red)71-114. All names are from a name generator and not actual names. Just trying to make the function work properly. names-x.pngnumbers-y.png

 

Thank you,

Dan

 

 

4 Replies
Since you've created a spreadsheet with false names, why don't you post it, rather than just posting an image. That way you wouldn't be forcing us to create our own copy....we could work with your anonymous data.
best response confirmed by Danimal513 (New Contributor)
Solution

@Danimal513 

Here is working sample

image.png

Conditional formatting rule formulae as

=INDEX($H$2:$H$5,MATCH(E2,$G$2:$G$5,0)) = "A"

 

Thank you so much! Since it was in google sheet I had to modify it slightly to;
=INDEX(indirect("Hires!$C$2:$C$115"),MATCH(E2,indirect("Hires!$B$2:$B$115"),0)) = "A"
being on a different tab. Super helpful, and much appreciated!!!

@Danimal513 , you are welcome