Looking for a Function

%3CLINGO-SUB%20id%3D%22lingo-sub-1656073%22%20slang%3D%22en-US%22%3ELooking%20for%20a%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1656073%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20lik%20to%20ask%20if%20there%20is%20a%20funcion%20that%20do%20the%20below.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20check%20if%20one%20shell%20belong%20to%20a%20table%20array%20and%20also%20if%20another%20shell%20bellog%20to%20the%20same%20array%20and%20if%20this%20two%20are%20true%20to%20give%20me%20a%20number%20that%20is%20next%20to%20him.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eas%20an%20example%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20check%20the%20A2%20and%20the%20A3%20if%20the%20belong%20to%20a%20different%20excel%20file%20table%20and%20if%20they%20are%20both%20true%20to%20give%20me%20the%20number%20that%20is%202%20sheets%20next%20to%20the%20A2%20number%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20for%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1656073%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-1657590%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1657590%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%2F789965%22%20target%3D%22_blank%22%3E%40tkollaros%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EIn%20the%20screenshot%20below%2C%20I%20created%20a%20table%20%22table1%22%20from%20cell%20D1%20to%20F13.%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20in%20cell%20B2%2C%20I%20added%20this%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(AND(IF(COUNTIF(Table1%2CA2)%3D1%2CTRUE%2CFALSE)%2C(IF(COUNTIF(Table1%2CA3)%3D1%2CTRUE%2CFALSE)))%2C2%2C%22%20%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESo%20if%20I%20type%20a%20number%20in%20cell%20A2%20and%20A3%20that%20exists%20in%20my%20table%2C%20cell%20B2%20returns%20%222%22.%20If%20not%2C%20it%20returns%20nothing.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bennadeau_0-1599834137723.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F217768iB35AE2C13B5609EF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Bennadeau_0-1599834137723.png%22%20alt%3D%22Bennadeau_0-1599834137723.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi all, 

 

I would lik to ask if there is a funcion that do the below. 

I want to check if one shell belong to a table array and also if another shell bellog to the same array and if this two are true to give me a number that is next to him.

 

as an example 

I want to check the A2 and the A3 if the belong to a different excel file table and if they are both true to give me the number that is 2 sheets next to the A2 number

 

thank you for your help

2 Replies

Hi @tkollaros,

In the screenshot below, I created a table "table1" from cell D1 to F13. 

Then in cell B2, I added this formula

=IF(AND(IF(COUNTIF(Table1,A2)=1,TRUE,FALSE),(IF(COUNTIF(Table1,A3)=1,TRUE,FALSE))),2," ")

So if I type a number in cell A2 and A3 that exists in my table, cell B2 returns "2". If not, it returns nothing. 

Bennadeau_0-1599834137723.png

 

 

Highlighted

@Bennadeau 

Cosmetic comment:

=IF(COUNTIF(Table1,A2)*COUNTIF(Table1,A3),2," ")

shall work same way