Home

Checking A Column

%3CLINGO-SUB%20id%3D%22lingo-sub-711336%22%20slang%3D%22en-US%22%3EChecking%20A%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711336%22%20slang%3D%22en-US%22%3ESo%20I've%20made%20the%20following%20formula%3CBR%20%2F%3E%3DIF(MATCH(B1%2CB2%3AB4%2C0)%2C%22True%22)%3CBR%20%2F%3EWhat%20I%20want%20to%20do%20is%20if%20it%20returns%20true%20I%20want%20it%20to%20compare%20the%20corresponding%20A%20column%20cell%20to%20A1%20and%20only%20return%20True%20if%20they%20both%20are%3CBR%20%2F%3ESo%20if%20my%20formula%20above%3D%20True%20because%20of%20B3%20then%20A1%20must%20equal%20A3%20for%20it%20to%20return%20the%20true%20value.%20How%20should%20I%20continue%20the%20formula.%20I%20did%20try%20Vlookup%20but%20I%20couldnt%20figure%20out%20how%20to%20phrase%20it%20in%20a%20conditional%20way.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-711336%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711399%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20A%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711399%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F73582%22%20target%3D%22_blank%22%3E%40Thomas%20Bryant%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24A%242%3A%24A%244%2C%20MATCH(B1%2C%24B%242%3A%24B%244%2C0))%2C%22no%20such%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711430%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20A%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711430%22%20slang%3D%22en-US%22%3EIt%20was%20a%20help.%20That%20formula%20returns%20the%20respective%20cell%20value.%20Now%20to%20fugure%20out%20how%20to%20get%20it%20to%20return%20False%20if%20the%20value%20isn't%20equal%20to%20A1.%20Heres%20an%20edit%20I%20just%20tried%20that%20didn't%20work%3CBR%20%2F%3E%3DIf((IFERROR(INDEX(%24A%242%3A%24A%244%2C%20MATCH(B1%2C%24B%242%3A%24B%244%2C0))%3DA1%2CTrue%2CFalse)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711495%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20A%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711495%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F73582%22%20target%3D%22_blank%22%3E%40Thomas%20Bryant%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24A%242%3A%24A%244%2C%20MATCH(B1%2CB2%3AB4%2C0))%3DA1%2CFALSE)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711527%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20A%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711527%22%20slang%3D%22en-US%22%3EI%20attached%20the%20file.%20For%20some%20reason%20it%20still%20returns%20False%20even%20though%20A1%20%26amp%3B%20B1%20should%20both%20be%20true%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711570%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20A%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711570%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F73582%22%20target%3D%22_blank%22%3E%40Thomas%20Bryant%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20you%20have%20%22Doreen%22%20in%20A1%20and%26nbsp%3B%22Doreen%20%22%20in%20A3.%20As%20variant%20it%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(TRIM(%24A%242%3A%24A%244)%2C%20MATCH(B1%26amp%3B%22*%22%2CB2%3AB4%2C0))%3DTRIM(A1)%2C%22TRUE%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711577%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20A%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711577%22%20slang%3D%22en-US%22%3ETwo%20things%3CBR%20%2F%3E1)%20OMG%20THANK%20YOU.%20YOU%20HAVE%20NO%20IDEA%20HOW%20MUCH%20MY%20HEAD%20HURT%20TRYING%20TO%20FIGURE%20IT%20OUT%3CBR%20%2F%3E2)%20I%20can't%20belive%20the%20problem%20was%20an%20invisible%20space.%20Excel%20is%20SENSITIVE%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711654%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20A%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711654%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F73582%22%20target%3D%22_blank%22%3E%40Thomas%20Bryant%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome.%20Yes%2C%20that%20could%20be%20not%20only%20spaces%20but%20other%20invisible%20characters%2C%20e.g.%20if%20you%20copy%2Fpaste%20from%20web.%20In%20most%20cases%20TRIM()%20and%20CLEAN()%20could%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711528%22%20slang%3D%22en-US%22%3ERe%3A%20Checking%20A%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711528%22%20slang%3D%22en-US%22%3EI%20attached%20the%20file.%20For%20some%20reason%20it%20still%20returns%20False%20even%20though%20A1%20%26amp%3B%20B1%20should%20both%20be%20true%3C%2FLINGO-BODY%3E
Thomas Bryant
Contributor
So I've made the following formula
=IF(MATCH(B1,B2:B4,0),"True")
What I want to do is if it returns true I want it to compare the corresponding A column cell to A1 and only return True if they both are
So if my formula above= True because of B3 then A1 must equal A3 for it to return the true value. How should I continue the formula. I did try Vlookup but I couldnt figure out how to phrase it in a conditional way.
8 Replies

@Thomas Bryant 

Perhaps

=IFERROR(INDEX($A$2:$A$4, MATCH(B1,$B$2:$B$4,0)),"no such")
It was a help. That formula returns the respective cell value. Now to fugure out how to get it to return False if the value isn't equal to A1. Heres an edit I just tried that didn't work
=If((IFERROR(INDEX($A$2:$A$4, MATCH(B1,$B$2:$B$4,0))=A1,True,False)

@Thomas Bryant 

 

It could be

=IFERROR(INDEX($A$2:$A$4, MATCH(B1,B2:B4,0))=A1,FALSE)
I attached the file. For some reason it still returns False even though A1 & B1 should both be true
I attached the file. For some reason it still returns False even though A1 & B1 should both be true

@Thomas Bryant 

 

That's since you have "Doreen" in A1 and "Doreen " in A3. As variant it could be

=IFERROR(INDEX(TRIM($A$2:$A$4), MATCH(B1&"*",B2:B4,0))=TRIM(A1),"TRUE")

 

Two things
1) OMG THANK YOU. YOU HAVE NO IDEA HOW MUCH MY HEAD HURT TRYING TO FIGURE IT OUT
2) I can't belive the problem was an invisible space. Excel is SENSITIVE

@Thomas Bryant 

 

You are welcome. Yes, that could be not only spaces but other invisible characters, e.g. if you copy/paste from web. In most cases TRIM() and CLEAN() could help.

Related Conversations