On Google Sheets, I would like to figure out a way to have a cell return the first value in column C where the same row in column D matches the criterion ("Blue" for example, as opposed to "Red"). I only need the value that is vertically the highest in column C whose row fits the criterion, since the column is already sorted. Additionally, I will be using this formula on a given sheet (ex. "Sheet A") but referring to data from another sheet which contains the criteria ("Sheet B" for example). Sorry, I'm a bit new to arrays and/or complicated formulae in Sheets.

  • 1
    Welcome. It's expected that question authors share a brief description of their search/research efforts. Please checkout How to Ask. Feb 18, 2019 at 3:22
  • Okay thanks for the heads up, I'll do that in the future. Feb 18, 2019 at 5:55

1 Answer 1

  • for numbers:

    =QUERY(C:D, "select C where D = 5 limit 1", 0)

  • for text:

    =QUERY(C:D, "select C where D = 'red' limit 1", 0)

  • Fantastic, thanks! This solved my problem. Feb 18, 2019 at 4:50
  • Why not VLOOKUP?
    – oshliaer
    Feb 25, 2019 at 13:58
  • 2
    @oshliaer could be too... like 2-3 other different ways. I didn't choose VLOOKUP coz it would require flipping columns eg QUERY is more pleasant to the eye in this scenario
    – user0
    Feb 25, 2019 at 14:18
  • I think limit 1 does the work fine. Thanks
    – oshliaer
    Feb 25, 2019 at 14:51

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.