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?


1 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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.