Finding values considering multiple conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2070737%22%20slang%3D%22en-US%22%3EFinding%20values%20considering%20multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070737%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%20I%20need%20a%20formula%20for%20this%20situation.%20I%20extracted%20a%20small%20fragment%20here%20and%20uploaded%20the%20excel%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Thank%20you!!!%3CBR%20%2F%3E%3CBR%20%2F%3EThere%20are%20Ids%20repeated%20according%20to%20other%20columns%2C%20so%20I%20need%20the%20STD%20value%20that%20considers%20the%20first%203%20columns.%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%22jfrjfr_0-1610975313549.png%22%20style%3D%22width%3A%20638px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247238i60693B420BC3801A%2Fimage-dimensions%2F638x85%3Fv%3D1.0%22%20width%3D%22638%22%20height%3D%2285%22%20role%3D%22button%22%20title%3D%22jfrjfr_0-1610975313549.png%22%20alt%3D%22jfrjfr_0-1610975313549.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2070737%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-2070848%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20values%20considering%20multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2070848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F932222%22%20target%3D%22_blank%22%3E%40jfrjfr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20E2%20as%20an%20array%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(M%242%3AM%241000%2CMATCH(1%2C(%24I%242%3A%24I%241000%3DA2)*(%24J%242%3A%24J%241000%3DB2)*(%24K%242%3A%24K%241000%3DC2)%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20data%20have%20more%20than%201000%20rows%2C%20adjust%20the%20number%201000%20in%20the%20formula.%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2077910%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20values%20considering%20multiple%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2077910%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThank%20you!%20Unfortunately%20its%20not%20working.%20Did%20you%20try%20it%20and%20worked%3F%20It%20looks%20perfect%2C%20but%20it%20does%20not%20work%20in%20my%20case%20%3CLI-EMOJI%20id%3D%22lia_confused-face%22%20title%3D%22%3Aconfused_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi! I need a formula for this situation. I extracted a small fragment here and uploaded the excel Thank you!!!

There are Ids repeated according to other columns, so I need the STD value that considers the first 3 columns.

 

jfrjfr_0-1610975313549.png

3 Replies

@jfrjfr 

In E2 as an array formula confirmed with Ctrl+Shift+Enter:

 

=INDEX(M$2:M$1000,MATCH(1,($I$2:$I$1000=A2)*($J$2:$J$1000=B2)*($K$2:$K$1000=C2),0))

 

If your data have more than 1000 rows, adjust the number 1000 in the formula.

Fill down.

@Hans Vogelaar Thank you! Unfortunately its not working. Did you try it and worked? It looks perfect, but it does not work in my case

@jfrjfr 

Most probably formula was entered without CSE. I modified a bit what @Hans Vogelaar suggested to use as regular formula

=INDEX(M$2:M$1000,MATCH(1,INDEX(($I$2:$I$1000=A2)*($J$2:$J$1000=B2)*($K$2:$K$1000=C2),0),0))