Which formula to use

%3CLINGO-SUB%20id%3D%22lingo-sub-1294898%22%20slang%3D%22en-US%22%3EWhich%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1294898%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20I'd%20like%20to%20try%20to%20make%20it%20myself%20so%20if%20possible%20I'd%20like%20some%20advice.%20I%20would%20like%20to%20know%20which%20formulas%20to%20use.%20attached%20is%20a%20file%20with%202%20sheets.%20in%20the%20sheet%20overview%20you%20have%20material%2C%20technique%20and%20multiple%20solutions.%20there%20needs%20to%20be%20a%20match%20with%20both%20material%2C%20technique%20and%20one%20of%20the%20solutions%20to%20present%20the%20solution%20in%20the%20overview%20sheet.%20the%20solution%20that%20has%20to%20be%20presented%20with%20the%20correct%20combination%20van%20be%20found%20in%20the%20sheet%20database%20column%20D.%20Could%20anyone%20tell%20me%20which%20formulas(or%20combination)%20to%20use%3F%20or%20show%20what%20could%20work%3F%20thank%20you%20in%20advance%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1294898%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-1294936%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1294936%22%20slang%3D%22en-US%22%3E%3CP%3EHIi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F135331%22%20target%3D%22_blank%22%3E%40Ramon%20Haagen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20you%20consider%20using%20PivotTable%20for%20this%20task%3F%3C%2FP%3E%3CP%3EYou%20could%20put%20Material%20and%20Technique%20as%20a%20filter%20and%20return%20results%20in%20rows%20field.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBR%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1294952%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1294952%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531305%22%20target%3D%22_blank%22%3E%40Branislav1984%3C%2FA%3EI%20tried%20but%20in%20the%20solution%20different%20categories%20I%20don't%20want%20the%20numbers%20but%20there%20should%20be%20a%20text%20displayed%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1295141%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1295141%22%20slang%3D%22en-US%22%3EHi%20Ramon%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThere%20shouldn't%20be%20any%20number%20if%20you%20put%20the%20solution%20and%20category%20in%20ROWS%20field%2C%20do%20not%20put%20anything%20into%20VALUES%20filed.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1297191%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1297191%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F135331%22%20target%3D%22_blank%22%3E%40Ramon%20Haagen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecould%20try%20index%20and%20match%20combination.%3CBR%20%2F%3Euse%20%22%26amp%3B%22%20to%20concatenate%20the%20material%20and%20technique%2C%20i.e.%20B1%26amp%3BB2%2C%20column%20B%20%26amp%3B%20column%20E%20in%20Database.%20And%20don't%20forget%20to%20use%20the%20Ctrl-Shift-Enter%20combination%20since%20it's%20array%20formula.%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%22HTsai_0-1586481790210.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F183529i96EABF74A070CF85%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22HTsai_0-1586481790210.png%22%20alt%3D%22HTsai_0-1586481790210.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1299194%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1299194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F135331%22%20target%3D%22_blank%22%3E%40Ramon%20Haagen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20XLOOKUP%20is%20available%20for%20your%20version%20of%20Excel%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DXLOOKUP(%24B%241%26amp%3B%24B%242%2CDatabase!%24B%242%3A%24B%2468%26amp%3BDatabase!%24E%242%3A%24E%2468%2CDatabase!%24D%242%3A%24D%2468%2C%22no%20such%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAnother%20variant%20is%20similar%20to%20suggested%2C%20but%20that's%20non-array%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFNA(INDEX(Database!%24D%242%3A%24D%2468%2CMATCH(1%2CINDEX((%24B%241%3DDatabase!%24B%242%3A%24B%2468)*(%24B%242%3DDatabase!%24E%242%3A%24E%2468)%2C0)%2C0))%2C%22no%20such%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EOne%20more%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFNA(LOOKUP(2%2C1%2F(%24B%241%3DDatabase!%24B%242%3A%24B%2468)%2F(%24B%242%3DDatabase!%24E%242%3A%24E%2468)%2CDatabase!%24D%242%3A%24D%2468)%2C%22no%20such%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1328152%22%20slang%3D%22en-US%22%3ERe%3A%20Which%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1328152%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20for%20the%20effort%20but%20Im%20not%20sure%20how%20to%20fix%20it%20if%20I%20have%20multiple%20lines%20of%20the%20same%20material%20and%20technique.%20then%20I%20would%20like%20that%20all%20solutions%20will%20be%20shown%20if%20both%20variables%20are%20the%20same(technique%20and%20material)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi all, I'd like to try to make it myself so if possible I'd like some advice. I would like to know which formulas to use. attached is a file with 2 sheets. in the sheet overview you have material, technique and multiple solutions. there needs to be a match with both material, technique and one of the solutions to present the solution in the overview sheet. the solution that has to be presented with the correct combination van be found in the sheet database column D. Could anyone tell me which formulas(or combination) to use? or show what could work? thank you in advance !

6 Replies
Highlighted

HIi @Ramon Haagen 

 

Did you consider using PivotTable for this task?

You could put Material and Technique as a filter and return results in rows field.

 

BR,

Highlighted

@Branislav1984I tried but in the solution different categories I don't want the numbers but there should be a text displayed

 

Highlighted
Hi Ramon,

There shouldn't be any number if you put the solution and category in ROWS field, do not put anything into VALUES filed.
Highlighted

Hi@Ramon Haagen 

 

could try index and match combination.
use "&" to concatenate the material and technique, i.e. B1&B2, column B & column E in Database. And don't forget to use the Ctrl-Shift-Enter combination since it's array formula.

 

HTsai_0-1586481790210.png

 

Highlighted

@Ramon Haagen 

If XLOOKUP is available for your version of Excel that could be

=XLOOKUP($B$1&$B$2,Database!$B$2:$B$68&Database!$E$2:$E$68,Database!$D$2:$D$68,"no such")

Another variant is similar to suggested, but that's non-array formula

=IFNA(INDEX(Database!$D$2:$D$68,MATCH(1,INDEX(($B$1=Database!$B$2:$B$68)*($B$2=Database!$E$2:$E$68),0),0)),"no such")

One more

=IFNA(LOOKUP(2,1/($B$1=Database!$B$2:$B$68)/($B$2=Database!$E$2:$E$68),Database!$D$2:$D$68),"no such")
Highlighted

@Sergei Baklan 

 

thank you for the effort but Im not sure how to fix it if I have multiple lines of the same material and technique. then I would like that all solutions will be shown if both variables are the same(technique and material)