Using Text from Cell with Validation Makes Formula Error

Question

I have a document set up with two tabs. Tab 1 has a list of foods (one food per row) with some info on each one across the columns. Tab 2 has a cell with a data validation where you can choose a food from Tab 1.

I'm attempting to use the following formula to get the entire row from Sheet 1 that corresponds to the food chosen in Tab 2:

=QUERY('Food List'!A5:G180, "where A = $A4 ")

This formula throws up an error, but if I substitute the text version of the food in (as shown here) it works perfectly:

=QUERY('Food List'!A5:G180, "where A = 'Bagel' ")

I have double checked with a simple =A4 and it comes up 'Bagel'.

Is there some way to get the text of a cell when working inside a formula?

Answer 1

Use the string concatenation operator &, and quote the cell value, like this:

=query('Food List'!A5:G, "where A = '" & A4 & "' ", 0)

You can also use filter(), like this:

=filter('Food List'!A5:G, 'Food List'!A5:A = A4)