Home

Confirming if a cell matches another using a vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-631884%22%20slang%3D%22en-US%22%3EConfirming%20if%20a%20cell%20matches%20another%20using%20a%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-631884%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20looking%20for%20some%20assistance%20with%20pulling%20data%20from%20one%20cell%20into%20another%20-%20the%20example%20would%20be%20as%20follows%3A%3C%2FP%3E%3CP%3ECell%20A1%3A%20ABC%20Brisbane%3C%2FP%3E%3CP%3ECell%20B1%3A%20Brisbane%3C%2FP%3E%3CP%3EI'm%20trying%20to%20generate%20a%20formula%20that%20will%20check%20if%20the%20word%20'Brisbane'%20is%20present%20in%20the%20first%20cell...%20My%20current%20formula%2C%20which%20hasn't%20worked%2C%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%3DIF(VLOOKUP(B1%2CA1%2C1)%3DB1%2C%22Match%22%2C%22No%20Match%22)%3C%2FP%3E%3CP%3EWhere%20am%20I%20going%20wrong%3F%20This%20formula%20returns%20a%20'No%20Match'%20no%20matter%20the%20cell%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-631884%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-632102%22%20slang%3D%22en-US%22%3ERe%3A%20Confirming%20if%20a%20cell%20matches%20another%20using%20a%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-632102%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F346171%22%20target%3D%22_blank%22%3E%40jacksoncheyne%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20more%20something%20like%3A%3C%2FP%3E%3CPRE%3E%3DIF(COUNTIFS(A1%2C%22*%22%26amp%3BB1)%3B%22%22%3B%22No%20%22)%26amp%3B%22Match%22%3C%2FPRE%3E%3CP%3EUntested.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-632126%22%20slang%3D%22en-US%22%3ERe%3A%20Confirming%20if%20a%20cell%20matches%20another%20using%20a%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-632126%22%20slang%3D%22en-US%22%3EYou%20can%20use%20a%20formula%20with%20(or%20without)%20wildcard%20characters%2C%20like%20this%3A%3CBR%20%2F%3EWith%20wildcard%3A%3CBR%20%2F%3E%3DIF(COUNTIF(A1%2C%22*%22%26amp%3BB1%26amp%3B%22*%22)%2C%3CBR%20%2F%3E%22Match%22%2C%3CBR%20%2F%3E%22No%20Match%22)%3CBR%20%2F%3EWithout%20wildcard%3A%3CBR%20%2F%3E%3DIF(ISNUMBER(SEARCH(B1%2CA1))%2C%3CBR%20%2F%3E%22Match%22%2C%3CBR%20%2F%3E%22No%20Match%22)%3CBR%20%2F%3EThe%20choice%20is%20yours!%3C%2FLINGO-BODY%3E
jacksoncheyne
Occasional Visitor

Hey guys,

 

Just looking for some assistance with pulling data from one cell into another - the example would be as follows:

Cell A1: ABC Brisbane

Cell B1: Brisbane

I'm trying to generate a formula that will check if the word 'Brisbane' is present in the first cell... My current formula, which hasn't worked, is as follows:

=IF(VLOOKUP(B1,A1,1)=B1,"Match","No Match")

Where am I going wrong? This formula returns a 'No Match' no matter the cell

 

Thanks in advance

2 Replies

@jacksoncheyne 

It is more something like:

=IF(COUNTIFS(A1,"*"&B1);"";"No ")&"Match"

Untested.

 

You can use a formula with (or without) wildcard characters, like this:
With wildcard:
=IF(COUNTIF(A1,"*"&B1&"*"),
"Match",
"No Match")
Without wildcard:
=IF(ISNUMBER(SEARCH(B1,A1)),
"Match",
"No Match")
The choice is yours!