Search code examples
sortinggoogle-sheetsfiltermatchvlookup

compare value across sheets and select the first value of a different column when the condition is met

I have two sheets the first is a data entry, the second creates a column of unique dates from the first sheet (this I have working). Next, I want to get the value in the first sheet where it matches the date in the second sheet. the problem is it returns multiple values and I only want the first for each date row matched.

here is a Google sheet example Example sheet in it sheet2 column B is what I'm looking to achieve.

on sheet2 I pull the dates from sheet1 using=SORT(UNIQUE(Flatten(Sheet1!A5:A))) this works.

Next, I want to take each date on sheet2 and get the row's first occurrence from sheet1 where the data matches and display the value in sheet1 column M in Sheet2 column B on the row where the date matches.

the example sheet hopefully makes it clearer!

my closest attempt was =IF('Sheet1'!$A$5:A = A1, 'Sheet1'!M5,"") but this doesn't work!

Sheet1 enter image description here

Sheet2

enter image description here

Solution

  • use:

    =SORTN({Sheet1!A5:A, Sheet1!M5:M}, 9^9, 2, 1, 1)

    enter image description here

    9^9   return all rows
2     group by
1     first column
1     in ascending order