Index multiple tables returning values based off OR results

%3CLINGO-SUB%20id%3D%22lingo-sub-2881286%22%20slang%3D%22en-US%22%3EIndex%20multiple%20tables%20returning%20values%20based%20off%20OR%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2881286%22%20slang%3D%22en-US%22%3E%3CP%3EHey%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20attempt%20I%20am%20making%20is%20trying%20to%20INDEX%203%20different%20Tables%2C%20returning%20rows%20that%20match%20criteria%20based%20on%203%20values.%20Any%20Row%20that%20has%20%22Approve%22%2C%20%22Add%22%2C%20or%20%22Keep%22%2C%20should%20return%20that%20row%20into%20this%20new%20table.%20When%20indexing%20a%20single%20table%20with%20a%20single%20criterion%20this%20works%20but%20when%20added%20multiple%20criteria%20it%20returns%20data%20that%20is%20not%20matching%20the%203%20criteria.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20sample%20code%20I%20have%20is%3CBR%20%2F%3E%3CBR%20%2F%3E%7B%3DIFERROR(INDEX('TABLE_1''!A%242%3AA%24544%2CSMALL(IF(OR('TABLE_1'!D2%3D'SHEET_1'!O%241%2C'TABLE_1'!D2%3D'SHEET_1'!O%242%2C'TABLE_1'!D2%3D'SHEET_1'!O%243)%2CROW('TABLE_1'!A%242%3AA%24544))%2CROW(1%3A1)%2C%22%22)%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20this%20current%20code%20is%20returning%20values%20that%20are%20not%20accepted%20in%20the%20IF%20OR%20statement.%20Is%20this%20a%20limitation%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2881286%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-2881577%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20multiple%20tables%20returning%20values%20based%20off%20OR%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2881577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1195110%22%20target%3D%22_blank%22%3E%40TLSOU%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(ISNUMBER(SEARCH(%24J%242%2CG2))%2CISNUMBER(SEARCH(%24J%243%2CG2))%2CISNUMBER(SEARCH(%24J%244%2CG2)))%2CROW()-1%2C%22%22)%3C%2FP%3E%3CP%3EI%20suggest%20this%20formula%20to%20compare%20if%26nbsp%3B%3CSPAN%3E%22Approve%22%2C%20%22Add%22%2C%20or%20%22Keep%22%20is%20found%20in%20any%20cell%20of%20range%20G2%3AG23%20in%20my%20example.%20Enter%20formula%20in%20C2%20and%20copy%20down%20and%20if%20there%20is%20a%20match%2C%20formula%20returns%20the%20position%20of%20the%20match%20within%20the%20array.%20This%20represents%20the%20row%20number%20within%20the%20array%20in%20the%20attached%20example.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20don't%20think%20it%20is%20possible%20to%20do%20this%20with%20INDEX%20as%20INDEX%20doesn't%20return%20a%20position%20but%20the%20value%20of%20a%20cell.%20An%20alternative%20might%20be%20doing%20this%20with%20MATCH%20function.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHowever%20i%20didn't%20understand%20what%20is%20meant%20by%20INDEX%203%20different%20Tables%20and%20by%20index%20multiple%20tables.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hey

 

So the attempt I am making is trying to INDEX 3 different Tables, returning rows that match criteria based on 3 values. Any Row that has "Approve", "Add", or "Keep", should return that row into this new table. When indexing a single table with a single criterion this works but when added multiple criteria it returns data that is not matching the 3 criteria. 

 

The sample code I have is

{=IFERROR(INDEX('TABLE_1''!A$2:A$544,SMALL(IF(OR('TABLE_1'!D2='SHEET_1'!O$1,'TABLE_1'!D2='SHEET_1'!O$2,'TABLE_1'!D2='SHEET_1'!O$3),ROW('TABLE_1'!A$2:A$544)),ROW(1:1),"")}

 

Again, this current code is returning values that are not accepted in the IF OR statement. Is this a limitation?

 

Any help appreciated

 

 

 

1 Reply

@TLSOU 

=IF(OR(ISNUMBER(SEARCH($J$2,G2)),ISNUMBER(SEARCH($J$3,G2)),ISNUMBER(SEARCH($J$4,G2))),ROW()-1,"")

I suggest this formula to compare if "Approve", "Add", or "Keep" is found in any cell of range G2:G23 in my example. Enter formula in C2 and copy down and if there is a match, formula returns the position of the match within the array. This represents the row number within the array in the attached example.