SOLVED

Formula for returning values from multiple criteria from multiple columns

%3CLINGO-SUB%20id%3D%22lingo-sub-3106541%22%20slang%3D%22en-US%22%3EFormula%20for%20returning%20values%20from%20multiple%20criteria%20from%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3106541%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20chance%20to%20use%20a%20formula%20to%20get%20unique%20result%20from%20several%20columns%20(G2%3AH4)%20based%20on%20multiple%20criteria%20(B%3AC)%3F%20In%20the%20%22F%22%20column%20are%20only%20unique%20records.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%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%22Sample.jpg%22%20style%3D%22width%3A%20477px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344686i29E335D37AE10FEA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Sample.jpg%22%20alt%3D%22Sample.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3106541%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-3106707%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20returning%20values%20from%20multiple%20criteria%20from%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3106707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F563911%22%20target%3D%22_blank%22%3E%40PavelDrg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((B2%3D%24F%242%3A%24F%244)*(C2%3D%24G%241%3A%24H%241)*%24G%242%3A%24H%244)%3C%2FP%3E%3CP%3E%3DINDEX(%24G%242%3A%24H%244%2CMATCH(B2%2C%24F%242%3A%24F%244%2C0)%2CMATCH(C2%2C%24G%241%3A%24H%241%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20with%20these%20formulas%20as%20shown%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3106903%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20returning%20values%20from%20multiple%20criteria%20from%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3106903%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20quick%20reaction!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%20I've%20got%20a%20problem%20with%20the%20formulas%2C%20maybe%20due%20to%20local%20language%20settings%20(but%20I%20checked%20twice).%20Could%20you%20please%20check%20on%20the%20linked%20xls%20file%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20updated%20the%20file%20structure.%20I%20will%20work%20with%2010k%20rows.%20Big%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20title%3D%22Sample%20xls%20file%22%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AnzEWP_xag4nheYZ3ZVqLAYNxRXr6Q%3Fe%3DLYEbyd%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fx%2Fs!AnzEWP_xag4nheYZ3ZVqLAYNxRXr6Q%3Fe%3DLYEbyd%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3107451%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20returning%20values%20from%20multiple%20criteria%20from%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3107451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F563911%22%20target%3D%22_blank%22%3E%40PavelDrg%3C%2FA%3E%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20The%20formula%20uses%20structured%20table%20references.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all,

 

Any chance to use a formula to get unique result from several columns (G2:H4) based on multiple criteria (B:C)? In the "F" column are only unique records.

 

Thanks in advance!

 

Sample.jpg

4 Replies

@PavelDrg 

=SUMPRODUCT((B2=$F$2:$F$4)*(C2=$G$1:$H$1)*$G$2:$H$4)

=INDEX($G$2:$H$4,MATCH(B2,$F$2:$F$4,0),MATCH(C2,$G$1:$H$1,0))

 

Maybe with these formulas as shown in the attached file.

@Quadruple_Pawn Thank you for your quick reaction!

 

Unfortunately I've got a problem with the formulas, maybe due to local language settings (but I checked twice). Could you please check on the linked xls file?

 

I updated the file structure. I will work with 10k rows. Big thanks!

 

https://1drv.ms/x/s!AnzEWP_xag4nheYZ3ZVqLAYNxRXr6Q?e=LYEbyd 

best response confirmed by PavelDrg (Occasional Contributor)
Solution

@PavelDrg

See the attached version. The formula uses structured table references.

Big thanks Hans! That's what I needed, you helped me a lot!