0

I have a funky lookup situation, consider this example data

Kris  |  Apr-2022  |   ?   |
Kris  |  May-2023  |   ?   |
Kris  |  Jun-2023  |   ?   |
Pat   |  May-2023  |   ?   |
Pat   |  Jun-2022  |   ?   |

In the last column, indicated by ?, I want to print the month where each person FIRST appeared in the data. So desired output given the above would be:

Kris  |  Apr-2022  |   Apr-2022   |
Kris  |  May-2023  |   Apr-2022   |
Kris  |  Jun-2023  |   Apr-2022   |
Pat   |  May-2023  |   Jun-2022   |
Pat   |  Jun-2022  |   Jun-2022   |

Now, IF the data set were ordered by date, I think I can do countifs(cell:end-of-column,cell), but the key here is that the data may be in ANY order. If that's the case, is it possible to do?

NOTE recognize that it may not be possible in one formula, happy to add helper columns with other formulas that make intermediate adjustments to the data to enable this. Will accept the answer that this is not possible to do, without sorting, I just don't have enough experience to say that. Also will not be able to do custom scripting.

2 Answers 2

0

With names in column A and dates in column B, use the following in C1:

=let(
names,tocol(A:A,1),
dates,tocol(B:B,1),
map(names,lambda(each,min(filter(dates,names=each)))))

We are defining two arrays names and dates for all the values in column A & B, then using map to return, for each row in names, the minimum value in dates after filtering to only return those dates where the corresponding rows in names match the row in question.

2
  • omg wow that's complex, and i had no idea these functions even existed! can you give me some quick bullets on what the function is doing?
    – james
    Commented Nov 27, 2023 at 20:42
  • I've added some explanation to the answer as requested. Commented Nov 27, 2023 at 22:09
0

The formulas below will both work. The QUERY version is much faster but a bit longer.

VLOOKUP is used in both to find the first date for the given name. Dates are presorted in the formula in ascending order.

=ARRAYFORMULA(
   IF(A:A<>"", VLOOKUP(A:A, 
     SORT(A:B,2,1), 2, 0),))

This is faster using QUERY to sort and fiter the data.

=ARRAYFORMULA(
 IF(A:A<>"", VLOOKUP(A:A,
   QUERY(A:B, "Where B is not Null
   Order by B"), 2, 0),))

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.