SOLVED
Home

Problem with a IF or Lookup function!

%3CLINGO-SUB%20id%3D%22lingo-sub-855749%22%20slang%3D%22en-US%22%3EProblem%20with%20a%20IF%20or%20Lookup%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855749%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20cell%20to%20be%20populated%20with%20the%20word%20Gateway%20if%20another%20cell%20has%20text%20from%20a%20column%20present.%3C%2FP%3E%3CP%3EIs%20this%20possible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei.e.%3C%2FP%3E%3CP%3E-%20Column%20U%20has%2020%20Company%20names.%3C%2FP%3E%3CP%3E-%20Column%20E%20will%20be%20populated%20with%20Company%20names.%3C%2FP%3E%3CP%3E-%20I%20want%20the%20cells%20in%20column%20C%20to%20be%20populated%20with%20the%20word%20'Gateway'%20when%20one%20of%20the%20Company%20Names%20in%20Column%20U%20is%20used.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%20Cassidy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-855749%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-855765%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20IF%20or%20Lookup%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855765%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408975%22%20target%3D%22_blank%22%3E%40johnjamescassidy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20below%20formula%20in%20column%20C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(IF(MATCH(E2%2C%24U%242%3A%24U%2421%2C0)%26gt%3B0%2C%22Gateway%22%2C%22%22)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20sample%20file%20is%20also%20attached%20for%20your%20reference..%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855767%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20IF%20or%20Lookup%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855767%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408975%22%20target%3D%22_blank%22%3E%40johnjamescassidy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20C2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(E2%3D%22%22%2C%22%22%2CIF(ISNA(MATCH(E2%2CU%3AU%2C0))%2C%22%22%2C%22Gateway%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20per%20the%20formula%2C%20if%20a%20cell%20in%20column%20E%20is%20empty%2C%20the%20formula%20will%20return%20a%20null%20string%20i.e.%20blank%20otherwise%20the%20formula%20will%20use%20MATCH%20function%20to%20look%20for%20the%20company%20name%20entered%20in%20column%20E%20and%20check%20whether%20it%20is%20present%20in%20column%20U%20or%20not%20and%20if%20a%20company%20match%20was%20not%20found%20the%20Match%20function%20will%20return%20%23N%2FA%20error%20to%20the%20outer%20function%20ISNA%20which%20will%20then%20be%20evaluated%20as%20true%20and%20the%20formula%20cell%20will%20return%20a%20blank%20again%20else%20if%20a%20company%20match%20is%20found%20in%20column%20U%2C%20the%20formula%20will%20return%20Gateway%20in%20column%20C.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855781%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20IF%20or%20Lookup%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855781%22%20slang%3D%22en-US%22%3EThank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-855938%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20a%20IF%20or%20Lookup%20function!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-855938%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20Welcome%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408975%22%20target%3D%22_blank%22%3E%40johnjamescassidy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
johnjamescassidy
Occasional Contributor

Hi,

I am looking for a cell to be populated with the word Gateway if another cell has text from a column present.

Is this possible.

 

i.e.

- Column U has 20 Company names.

- Column E will be populated with Company names.

- I want the cells in column C to be populated with the word 'Gateway' when one of the Company Names in Column U is used.

 

Regards,

 

John Cassidy

4 Replies
Solution

Hi @johnjamescassidy 

 

You can use below formula in column C

 

=IFERROR(IF(MATCH(E2,$U$2:$U$21,0)>0,"Gateway",""),"")

 

A sample file is also attached for your reference..

Thanks

Tauqeer

@johnjamescassidy 

You may try this...

 

In C2

=IF(E2="","",IF(ISNA(MATCH(E2,U:U,0)),"","Gateway"))

and copy it down.

 

As per the formula, if a cell in column E is empty, the formula will return a null string i.e. blank otherwise the formula will use MATCH function to look for the company name entered in column E and check whether it is present in column U or not and if a company match was not found the Match function will return #N/A error to the outer function ISNA which will then be evaluated as true and the formula cell will return a blank again else if a company match is found in column U, the formula will return Gateway in column C.

 

You're Welcome@johnjamescassidy 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies