Using a cell to fill out/finish a formula

Question

So I am currently making a calculator, but need to make it as simple as possible. Currently I have it like this:

=IFERROR(query('Calculator Data'!B:H,"Select F where C='105 Kira Tusami'", ""))

If they want to calculate something else, they'd have to CTRL+F, and replace "105 Kira Tusami" with the number and the name of the person they need to calculate things for.

I am trying to find a way where they can type the number + name in to a cell, and it would automatically put that cell's content in to the formula.

So for an example:

Calculator!B6 = 105 Kira Tusami

=IFERROR(query('Calculator Data'!B:H,"Select F where C='Calculator!B6'", ""))

Obviously this doesnt work and I played around with it to see how I can get it to work, but can't seem to figure it out.

Answer 1

For something like this, it's often easier to use FILTER:

=IFERROR(FILTER('Calculator Data'!F:F, 'Calculator Data'!C:C=Calculator!B6))

However, if you wanted to stick with QUERY, write the WHERE clause like this:

WHERE C='"&Calculator!B6&"'"

If there will only ever be one possible match, then you'd be better off using VLOOKUP:

=IFERROR(VLOOKUP(Calculator!B6,'Calculator Data'!C:F,4,FALSE))