My Query function keeps returning a #VALUE error and I can't figure out what I'm doing wro-CodePudding

So I have this function

 =QUERY({PAScore;DetPA;DetLabel}, "SELECT Col3 WHERE Col2 < '" & Input!P31 & "' LIMIT 1", 0)

That's meant to return a string from DetLabel or Col3 where DetPA or Col2 is less than Input!P31(which is located in PAScore). Instead, I get an error message that says "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col3."

DetPA and DetLabel are on the same sheet while PAScore is on a different one. DetLabel contains strings while the other two contain values.

I've been messing around with this for a couple of hours now and can't seem to figure it out, so if anyone knows what's wrong, any help would be appreciated!

If more info is needed, please let me know! I'll do my best to provide it :)

As requested, here is a copy of the sheet. The formula in question is on sheet Data in cell B31. The purpose of this formula is to do basically the same thing the cells above it do, but without the formatting and with a simpler and more adjustable formula.

https://docs.google.com/spreadsheets/d/1gKfEJzR4lvQJJUoA1tqYF4jMSgj2VApLQ-jnGgK3_CM/edit?usp=sharing

The accepted answer solved my problem, but for anyone else reading with a similar problem, I did have to change the ranges so that they all had the same amount of rows :D

CodePudding user response：

asuming that each of {PAScore; DetPA; DetLabel} has 3 columns and Input!P31 is numeric, use:

=QUERY({PAScore; DetPA; DetLabel}, 
 "select Col3 
  where Col2 < "&Input!P31&" 
  limit 1", 0)

if each of {PAScore; DetPA; DetLabel} has sonly one column use:

=QUERY({PAScore, DetPA, DetLabel}, 
 "select Col3 
  where Col2 < "&Input!P31&" 
  limit 1", 0)