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

Question

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

Answer 1

There is a few things that need to correct in your original code - here is my code for 3 files global.R, server.R, and ui.R with detail explanation comments. (my habit of separating them so it easier to manage.

global.R

#load libraries, data
library(shiny)
library(tidyr)
library(readxl)
library(dplyr)
library(purrr)

# This is just a generation of sample data to be used in this answer.
set.seed(1)
generate_random_df <- function(name) {
  tibble(
    product = paste0(name, "-", round(runif(n = 10, min = 1, max = 100))),
    price = runif(10))
}
a <- generate_random_df("a")
b <- generate_random_df("b")
c <- generate_random_df("c")
mylist <- list(a = a, b = b, c = c)

server.R

set.seed(1)
generate_random_df <- function(name) {
  tibble(
    product = paste0(name, "-", round(runif(n = 10, min = 1, max = 100))),
    price = runif(10))
}
a <- generate_random_df("a")
b <- generate_random_df("b")
c <- generate_random_df("c")
mylist <- list(a = a, b = b, c = c)

server <- function(input, output, session) {
  
  updateSelectInput(session,
                    "type",
                    choices = names(mylist))
  
  # to extract the data you need to reference to mylist as the Input only take
  # the name of your list not the dataset within it
  price <- reactive({
    # Here the material command also inside the reactive not as you do initially
    material <- mylist[[input$type]]
    
    paste0(material[1,"price"])
  })
  
  # You don't need renderText for this just assign the value to message
  output$message <- price
  
  # I also output the table for easier to see
  output$price_table <- renderTable(mylist[[input$type]])
}

ui.R

ui <- navbarPage(
  "Dashboard",
  tabPanel(
    "Cost1",
    fluidPage(
      selectInput("type",
                  label = "Select Type",
                  choices = NULL),
      textOutput("message"),
      tableOutput("price_table")
    )
  )
)

Here is the screenshot of the app