# Which formula to use

Highlighted
Occasional Contributor

# Which formula to use

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

# Re: Which formula to use

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

# Re: Which formula to use

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

Highlighted

# Re: Which formula to use

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

# Re: Which formula to use

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.

Highlighted

# Re: Which formula to use

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

# Re: Which formula to use

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)