Apr 09 2020 01:03 AM
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 !
Apr 09 2020 01:21 AM
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,
Apr 09 2020 01:28 AM
@Branislav1984I tried but in the solution different categories I don't want the numbers but there should be a text displayed
Apr 09 2020 03:11 AM
Apr 09 2020 06:24 PM
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.
Apr 10 2020 03:51 PM
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")
Apr 22 2020 03:02 AM
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)