If Statement for Value Pasting to another Tab

%3CLINGO-SUB%20id%3D%22lingo-sub-1764873%22%20slang%3D%22en-US%22%3EIf%20Statement%20for%20Value%20Pasting%20to%20another%20Tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1764873%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20attempting%20to%20write%20an%20If%20statement%20for%20transferring%20data%20in%20one%20cell%20to%20one%20in%20another%20tab.%20The%20logical%20test%20is%20whether%20a%20cell%20in%20the%20same%20row%20(%20In%20the%20tab%20being%20transferred%20to)%20contains%20two%20or%20more%20of%20the%20same%20words%20as%20a%20cell%20in%20the%20same%20row%20as%20the%20cell%20from%20which%20the%20data%20is%20being%20transferred.%20If%20there%20is%20a%20match%20of%20two%20or%20more%20words%20then%20the%20data%20should%20be%20transferred%20and%20left%20blank%20otherwise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20appreciate%20any%20help%20on%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1764873%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-1765024%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Statement%20for%20Value%20Pasting%20to%20another%20Tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1765024%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F826609%22%20target%3D%22_blank%22%3E%40msoutherland%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20generate%20small%20sample%20file%20to%20illustrate%20the%20logic%2C%20it's%20not%20clear%20enough.%20Most%20probably%20that's%20not%20IF().%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1765287%22%20slang%3D%22en-US%22%3ERe%3A%20If%20Statement%20for%20Value%20Pasting%20to%20another%20Tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1765287%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20timely%20response.%20I%20have%20attached%20a%20sample%20file.%20I%20am%20attempting%20to%20transfer%20the%20contents%20of%20Tab%202%20column%203%20to%20cells%20in%20column%207%20of%20Tab%201.%20The%20criteria%20for%20which%20the%20value%20should%20be%20transferred%20is%20whether%20the%20value%20in%20column%201%20of%20the%20same%20row%20as%20the%20value%20to%20be%20transferred%20(In%20tab%202)%20contains%20at%20least%20two%20matching%20words%20with%20column%201%20in%20tab%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIe.%20Cell%20G3%20of%20tab%201%20should%20be%20populated%20with%20%22Microsoft%22%20by%20this%20logic.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi there,

 

I'm attempting to write an If statement for transferring data in one cell to one in another tab. The logical test is whether a cell in the same row ( In the tab being transferred to) contains two or more of the same words as a cell in the same row as the cell from which the data is being transferred. If there is a match of two or more words then the data should be transferred and left blank otherwise.

 

Would appreciate any help on this. 

 

Cheers!

 

3 Replies
Highlighted

@msoutherland 

Perhaps you may generate small sample file to illustrate the logic, it's not clear enough. Most probably that's not IF().

Highlighted

Hi @Sergei Baklan,

 

Thank you for the timely response. I have attached a sample file. I am attempting to transfer the contents of Tab 2 column 3 to cells in column 7 of Tab 1. The criteria for which the value should be transferred is whether the value in column 1 of the same row as the value to be transferred (In tab 2) contains at least two matching words with column 1 in tab 1.

 

Ie. Cell G3 of tab 1 should be populated with "Microsoft" by this logic.

Highlighted

@msoutherland 

Afraid that's not feasible with formulas - check if any two words in one texts contains in another text. That will require some programming. And for it logic is not clear, e.g. what consider as the word - any part of the text with space around, or it could be separated by slash, comma, etc. Plus possible misprint in texts as in your sample: Bald Mountian and Bald Mountain are not equal, to compare them fuzzy logic approach is required.

 

Other words as such that's not obvious and not simple task. Perhaps logic could be simplified, for example if return the value from tab 2 against text from it contains (as a full, not any parts of it) in tab 1, and to correct texts, that could be like

=INDEX('Tab 2'!$C$2:$C$5,XMATCH(1,--ISNUMBER(SEARCH('Tab 2'!$A$2:$A$5,$A$2:$A$3))))