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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies