Can you use AND / OR in an INDEX MATCH

%3CLINGO-SUB%20id%3D%22lingo-sub-121691%22%20slang%3D%22en-US%22%3ECan%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-121691%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20have%20am%20array%20formula%20that%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX('Rebate%20report'!A%3AA%2CMATCH(1%2C('Rebate%20report'!A%3AA%3DA2)*('Rebate%20report'!B%3AB%3DB2)*('Rebate%20report'!C%3AC%3DC2)%2C0)%2C1)%20which%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20add%20in%20an%20OR%20function%20for%20the%20name%20in%20column%20A.%20I%20will%20add%20this%20name%20in%20Column%20T.%20In%20other%20words%20the%20match%20is%20correct%20if%20column%20A%20or%20T%20match%20A2%20and%20COL%20B%3DB2%20and%20COL%20C%3DC2%20also%20match%20the%20criteria%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20using%20the%20%2B%20to%20add%20T%20criteria%20but%20gave%20me%20a%200%3C%2FP%3E%3CP%3E%3DINDEX('Rebate%20report'!A%3AA%2CMATCH(1%2C('Rebate%20report'!A%3AA%3DA2)*('Rebate%20report'!B%3AB%3DB2)*('Rebate%20report'!C%3AC%3DC2)%2B('Rebate%20report'!A%3AA%3DT2)%2C0)%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-121691%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-123202%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-123202%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Sergei!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-122875%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-122875%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20David%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMore%20exactly%3C%2FP%3E%3CPRE%3E(A%3DA2%20OR%20A%3DT2)%20AND%20(B%20%3D%20B2%20AND%20C%3DC2)%3C%2FPRE%3E%3CP%3E%2C%20that%20was%20my%20misprint.%20In%20this%20case%20array%20formula%20could%20be%3C%2FP%3E%3CPRE%3E%3DIFERROR(INDEX('Rebate%20report'!A%3AA%2CMATCH(1%2C((('Rebate%20report'!A%3AA%3DA2)%2B('Rebate%20report'!A%3AA%3DT2))%26gt%3B0)*('Rebate%20report'!B%3AB%3DB2)*('R'!C%3AC%3DC2)%2C0)%2C1)%2C0)%3C%2FPRE%3E%3CP%3EFirst%20multiplier%20in%20MATCH%20imitates%20OR%20condition%2C%20next%20are%20with%20AND%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-122836%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-122836%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20one%20is%20where%20the%20OR%20should%20be.%20Thanks%3C%2FP%3E%3CP%3E(A%3DA2%20OR%20t%3DA2)%20AND%20(B%20%3D%20B2%20AND%20C%3DC2)%2C%3C%2FP%3E%3CDIV%20class%3D%22lia-page%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-121890%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-121890%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20David%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20clarify%20first%20one%2C%20where%20is%20OR%3F%3C%2FP%3E%3CP%3E%3CSPAN%3EA%3DA2%20OR%20(t%3DA2%20AND%26nbsp%3BB%20%3D%20B2%20AND%26nbsp%3BC%3DC2)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E(A%3DA2%20OR%20t%3DA2)%20AND%20(B%20%3D%20B2%20AND%26nbsp%3BC%3DC2)%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F44944%22%20target%3D%22_blank%22%3E%40David%3C%2FA%3E%20wrote%3A%3CBR%20%2F%3E%3CP%3Eif%26nbsp%3BA%3DA2%20OR%20t%3DA2%20AND%26nbsp%3BB%20%3D%20B2%20AND%26nbsp%3BC%3DC2%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ereturn%20a%20cell%26nbsp%3Bref%20for%20name%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-121888%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-121888%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Sergei%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20second%20statement%20is%20not%20what%20I%20am%20trying%20to%20do.%3C%2FP%3E%3CP%3EI%20want%20it%20to%20give%20a%20name%20in%20Column%20from%20the%26nbsp%3B%20Rebate%20Report%20if%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%26nbsp%3BA%3DA2%20OR%20t%3DA2%20AND%26nbsp%3BB%20%3D%20B2%20AND%26nbsp%3BC%3DC2%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ereturn%20a%20cell%26nbsp%3Bref%20for%20name%3C%2FP%3E%3CP%3Eif%26nbsp%3BA%3DA2%20AND%20T%3DA2%20AND%26nbsp%3BB%3DB2%20AND%26nbsp%3BC%3DC2%20return%20a%20cell%20ref%20for%20name.%20This%20should%20return%20a%20ref%20and%20not%20NA.%20This%20seemed%20different%20from%20what%20you%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Esaid%20it%20would%20do%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ein%20the%20formula.%3C%2FP%3E%3CP%3EIf%20A%20not%20match%26nbsp%3BA2%20AND%20T%20also%20not%20match%20A2%20OR%20B%20not%20match%20B2%20OR%20C%20not%20match%20C2%20then%20return%20NA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20this%20makese%20sense.%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-121727%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-121727%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20David%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20it%20returns%200%20that's%20something%20in%20your%20figures.%20Formula%20works%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20ABC%20or%20T%20matches%20it%20return%20some%20number%3C%2FP%3E%3CP%3Eif%20ABC%20and%20T%20matches%20it%20returns%20%23N%2FA%3C%2FP%3E%3CP%3Eif%20no%20one%20of%20ABC%20and%20T%20matches%20it%20returns%20%23N%2FA%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-SUB%20id%3D%22lingo-sub-1822449%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1822449%22%20slang%3D%22en-US%22%3E%3CP%3E%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%26nbsp%3B%20I%20read%20thiis%20old%20example%20and%20it%20seems%20to%20have%20worked.%20But%2C%20all%20I%20needed%20was%20a%20guide%20to%20use%20just%20OR%20in%20MATCHes%20(the%20addition%20of%20ANDs%20in%20the%20example%20got%20me%20confused%20on%20the%20brackets%20and%201%2Fzeros%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%20I%20need%20a%20way%20for%20a%20user%20to%20enter%20a%20dashboard%20cell%20with%20any%20of%203%20simple%20texts%20-%20and%20for%20whatever%20they%20enter%20be%20MATCHed%20against%203%20columns%20(B%3AB%20engine%20codes%2C%20or%20C%3AC%20job%20codes%2C%20or%20D%3AD%20part%20number)%20-%20if%20there's%20ANY%20mach%20-%20it%20is%20passed%20to%20INDEX%20to%20find%20the%20generic%20Account%20ID%20from%20A%3AA%20column.%20Many%20Thanks%20for%20any%20simple%20guidance%20JW%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1824766%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1824766%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847316%22%20target%3D%22_blank%22%3E%40Johnny_Warks27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20values%20in%20these%203%20columns%20are%20always%20different%20(i.e.%20engine%20code%20never%20could%20be%20equal%20job%20code%2C%20etc.)%20variant%20of%20the%20formula%20is%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20712px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229615i0C7764E26190C9E0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(E3%3AE14%2CMATCH(1%2CINDEX(%20(B3%3AB14%3DH3)%2B(C3%3AC14%3DH3)%2B(D3%3AD14%3DH3)%2C0)%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1832530%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1832530%22%20slang%3D%22en-US%22%3E%3CP%3E%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%26nbsp%3B%3C%2FP%3E%3CP%3EOh%20my%2C%20Sergei%2C%20that's%20a%20winning%20solution!%3CBR%20%2F%3EI%20had%20researched%20having%20an%20Index%20within%20the%20Index%20but%20failed%20my%20attempts.%3CBR%20%2F%3EIn%20addition%2C%20I%20simply%20did%20not%20realise%20each%20MATCH%20event%20would%20need%20to%20%22%3DH3%22%20so%20that%20each%20event%20is%20resolved%20(y%2Fn%2C%200%2F1)%20Now%20I%20see%20it%2C%20the%20logic%20is%20obvious.%3CBR%20%2F%3Emy%20sincere%20thanks.%20bolshoy%20spasiba%20Sergei%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1834940%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1834940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847316%22%20target%3D%22_blank%22%3E%40Johnny_Warks27%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20ne%20za%20chto%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2032111%22%20slang%3D%22es-ES%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2032111%22%20slang%3D%22es-ES%22%3E%3CP%3E%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%26nbsp%3BHi!!!%20I%20have%20the%20next%20sheets%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22RODRI1611_0-1609837665354.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F244243i84DB4BF7389F271C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22RODRI1611_0-1609837665354.png%22%20alt%3D%22RODRI1611_0-1609837665354.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eand%20the%20other%20one%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22RODRI1611_1-1609837709310.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F244244i2772E163321192E7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22RODRI1611_1-1609837709310.png%22%20alt%3D%22RODRI1611_1-1609837709310.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20that%20gives%20me%20the%20value%20of%20the%20cell%20C3%20from%20sheet%202%20if%20b3%20from%20sheet1%20%3F%20a3%20from%20sheet2%20and%20c3%20sheet1%20%3F%20b3%20sheet%202%20and%20e3%20sheet%201%20'd3%20sheet2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20muchin%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2032148%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2032148%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F918146%22%20target%3D%22_blank%22%3E%40RODRI1611%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20transform%20your%20wording%20into%20IF()%20formula%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3Ethe%20cell%20C3%20from%20sheet%202%0Aif%20b3%20from%20sheet1%20%3D%20a3%20from%20sheet2%20and%20c3%20sheet1%20%3D%20b3%20sheet%202%20and%20e3%20sheet%201%20%3Dd3%20sheet2%0A%0A%3D%26gt%3B%0A%0A%3DIF(%20(B3%3DSheet2!A3)*(C3%3DSheet2!B3)*(E3%3DSheet2!D3)%2C%0A%20%20%20%20%20Sheet2!C3%2C%0A%20%20%20%20%20%22no%20result%22%0A)%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2032503%22%20slang%3D%22es-ES%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2032503%22%20slang%3D%22es-ES%22%3E%3CP%3E%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%26nbsp%3BThanks%20a%20lot!!!%20The%20thing%20is%20I%20have%20two%20sheets%20with%20a%20table%20in%20each%2C%20and%20what%20I%20need%20is%20that%20excel%20returns%20the%20value%20of%20cells%20of%20column%20C%20from%20sheet%202%2C%20provided%20the%20above%20conditions%20are%20met.%3C%2FP%3E%3CP%3Eie%20looks%20for%20the%203%20matches%20of%20table%201%20in%20table%202%20and%20if%20it%20finds%20them%2C%20it%20shows%20the%20corresponding%20value%20of%20column%20C%20from%20table%202%20in%20g3%20in%20table%201%2C%20and%20apply%20all%20this%20to%20all%20the%20records%20in%20table%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20gain%20for%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2032566%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2032566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F918146%22%20target%3D%22_blank%22%3E%40RODRI1611%3C%2FA%3E%26nbsp%3B%2C%20not%20sure%20I%20understood%20entire%20logic%2C%20better%20with%20sample%20file.%20Anyway%2C%20will%20be%20glad%20if%20I%20was%20able%20to%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2032614%22%20slang%3D%22es-ES%22%3ERe%3A%20Can%20you%20use%20AND%20%2F%20OR%20in%20an%20INDEX%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2032614%22%20slang%3D%22es-ES%22%3E%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%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%26nbsp%3BOk!!!%20There%20you%20are!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22RODRI1611_0-1609850180195.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F244269i8FFD161AD8D9331B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22RODRI1611_0-1609850180195.png%22%20alt%3D%22RODRI1611_0-1609850180195.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%202%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22RODRI1611_1-1609850248350.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F244270iB47DA0B2D5271A84%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22RODRI1611_1-1609850248350.png%22%20alt%3D%22RODRI1611_1-1609850248350.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20Excel%20to%20return%20in%20column%20P%20of%20sheet%201%2C%20the%20value%20of%20corresponding%20register%20from%20column%20B%20of%20sheet%202%2C%20provided%20that%20excel%20finds%20Sheet1!%20B2%20in%20Sheet2!column%20A%2C%20and%20Sheet1!%20N2%20in%20Sheet2!column%20C%2C%20and%20Sheet1!%20W2%20in%20Sheet2!column%20E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi I have am array formula that looks like this:

 

=INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2),0),1) which works.

 

I want to add in an OR function for the name in column A. I will add this name in Column T. In other words the match is correct if column A or T match A2 and COL B=B2 and COL C=C2 also match the criteria

 

I tried using the + to add T criteria but gave me a 0

=INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2)+('Rebate report'!A:A=T2),0),1)

 

Thanks for the help!

20 Replies

Hi David,

 

If it returns 0 that's something in your figures. Formula works like

 

if ABC or T matches it return some number

if ABC and T matches it returns #N/A

if no one of ABC and T matches it returns #N/A

 

 

 

Thanks Sergei

 

Your second statement is not what I am trying to do.

I want it to give a name in Column from the  Rebate Report if:

 

if A=A2 OR t=A2 AND B = B2 AND C=C2 return a cell ref for name

if A=A2 AND T=A2 AND B=B2 AND C=C2 return a cell ref for name. This should return a ref and not NA. This seemed different from what you said it would do in the formula.

If A not match A2 AND T also not match A2 OR B not match B2 OR C not match C2 then return NA.

 

Let me know if this makese sense. Thanks.

Hi David,

 

Just to clarify first one, where is OR?

A=A2 OR (t=A2 AND B = B2 AND C=C2)

(A=A2 OR t=A2) AND (B = B2 AND C=C2),

...

 


@David wrote:

if A=A2 OR t=A2 AND B = B2 AND C=C2 return a cell ref for name

 

Hi Sergei,

 

The second one is where the OR should be. Thanks

(A=A2 OR t=A2) AND (B = B2 AND C=C2),

 

Hi David,

 

More exactly

(A=A2 OR A=T2) AND (B = B2 AND C=C2)

, that was my misprint. In this case array formula could be

=IFERROR(INDEX('Rebate report'!A:A,MATCH(1,((('Rebate report'!A:A=A2)+('Rebate report'!A:A=T2))>0)*('Rebate report'!B:B=B2)*('R'!C:C=C2),0),1),0)

First multiplier in MATCH imitates OR condition, next are with AND

Thanks Sergei!

@Sergei Baklan  I read thiis old example and it seems to have worked. But, all I needed was a guide to use just OR in MATCHes (the addition of ANDs in the example got me confused on the brackets and 1/zeros   I need a way for a user to enter a dashboard cell with any of 3 simple texts - and for whatever they enter be MATCHed against 3 columns (B:B engine codes, or C:C job codes, or D:D part number) - if there's ANY mach - it is passed to INDEX to find the generic Account ID from A:A column. Many Thanks for any simple guidance JW

@Johnny_Warks27 

Assuming values in these 3 columns are always different (i.e. engine code never could be equal job code, etc.) variant of the formula is

image.png

=INDEX(E3:E14,MATCH(1,INDEX( (B3:B14=H3)+(C3:C14=H3)+(D3:D14=H3),0),0))

@Sergei Baklan 

Oh my, Sergei, that's a winning solution!
I had researched having an Index within the Index but failed my attempts.
In addition, I simply did not realise each MATCH event would need to "=H3" so that each event is resolved (y/n, 0/1) Now I see it, the logic is obvious.
my sincere thanks. bolshoy spasiba Sergei

@Johnny_Warks27 , you are welcome, ne za chto

@Sergei Baklan Hi !!! I have the next sheets

RODRI1611_0-1609837665354.png

and the other one

RODRI1611_1-1609837709310.png

I need a formula that gives me the value of the cell C3 from sheet 2 if b3 from sheet1 = a3 from sheet2 and c3 sheet1 = b3 sheet 2 and e3 sheet 1 =d3 sheet2

 

Thank you very muchin advance

 

@RODRI1611 

You may transform your wording into IF() formula as

the cell C3 from sheet 2
if b3 from sheet1 = a3 from sheet2 and c3 sheet1 = b3 sheet 2 and e3 sheet 1 =d3 sheet2

=>

=IF( (B3=Sheet2!A3)*(C3=Sheet2!B3)*(E3=Sheet2!D3),
     Sheet2!C3,
     "no result"
)

@Sergei Baklan Thanks a lot!!! The thing is I have two sheets with a table in each, and what I need is that excel returns the value of cells of column C from sheet 2, provided the above conditions are met.

ie  looks for the 3 matches of table 1 in table 2 and if it finds them, it shows the corresponding value of column C from table 2 in g3 in table 1, and apply all this to all the records in table 1

 

Thank you gain for your help

@RODRI1611 , not sure I understood entire logic, better with sample file. Anyway, will be glad if I was able to help.

Spoiler
 

@Sergei Baklan OK!!! There you are!!!

 

Sheet 1

 

RODRI1611_0-1609850180195.png

 

Sheet 2 

 

RODRI1611_1-1609850248350.png

 

I want Excel to return in column P of sheet 1, the value of corresponding register from column B of sheet 2, provided that excel finds Sheet1!B2 in Sheet2!column A, and Sheet1!N2 in Sheet2!column C, and Sheet1!W2 in Sheet2!column E

 

Thanks again

 

@RODRI1611 

Okay, thank you. Could you please clarify if XLOOKUP() is available for your version of Excel or not.

I´m afraid not
Just:
Buscar
BuscarV
BuscarH

@RODRI1611 

Okay, when INDEX/MATCH/INDEX could work like

=IFNA(INDEX(Sheet2!$B$2:$B$16, MATCH(1, INDEX((B2=Sheet2!$A$2:$A$16)*(N2=Sheet2!$C$2:$C$16)*(W2=Sheet2!$E$2:$E$16),0),0)),"no such")

as in attached sample. If you open the file formula will be converted for your locale. Please adjust actual ranges. You may use something like Sheet2!A:A, etc instead of Sheet2!$A2$A16, but that affects performance. Or use dynamic range, even better to use Excel Tables.

@Sergei Baklan It worked properly!!!

Thanks soooo much!!!