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.
-
1Welcome. It's expected that question authors share a brief description of their search/research efforts. Please checkout How to Ask.– Rubén ♦Feb 18, 2019 at 3:22
-
Okay thanks for the heads up, I'll do that in the future.– thunder_riverFeb 18, 2019 at 5:55
Add a comment
|
1 Answer
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)
-
-
-
2@oshliaer could be too... like 2-3 other different ways. I didn't choose
VLOOKUPcoz it would require flipping columns egQUERYis more pleasant to the eye in this scenario– user0Feb 25, 2019 at 14:18 -