Loop a comparison and on match return earliest date

[MrPsikick]

Dear all,

Although somewhat of a VBA novice, I am not completely inexperienced with coding. Using online examples I am usually capable to reconstruct whatever I need. However, it's the combination of things I need to do now, which has left me banging my head on the keyboard for days now

Here's the data I am working with:

Sheet 1:

Sheet 2:

And here's what I need to get done:

1. From sheet 1, select first ID entry from Column A.
2. Find matching ID in Column A of sheet 2
3. On match, find in Column B the earliest date belonging to the concerned ID
4. Copy that earliest date next to the corressponding ID in Column B back on sheet 1
5. Return to step 1 and repeat for next ID entry. Do until end of list (sheet1)

So the result should look as following on sheet 1:

The major issue I am having is the combination of step 3 and 5. Because it probably means something like moving through an array that's within an array through which one is moving. And I am just missing that bit of experience that allows me to make that thinking step. I just keep falling off if you know what I mean...

I'd really appreciate any help!

 

[Jonmo1]

Welcome to the board..

Try this array formula enterd with CTRL + SHIFT + ENTER

=MIN(IF(Sheet2!$A$1:$A$6=A1,Sheet2!$B$1:$B$6))

 

[MrPsikick]

Holy crap!

Of course! Thanks a million! ray: You have no idea how much headache this one gave me

I actually saw that answer at other places before. But somehow I had my mind set on doing it fancy (doing it only at the press of a button). Therefore I was messing around in VBA which got me completely tangled up in all other sorts of issues.

To be honest because of that I didn't take your answer seriously at first, but then I tried it anyway and it works! The heck with fancy, this right here. This. This is elegance.

Do you (or anyone) perhaps also know how to let it return custom string on no match rather than a 00-01-00 date?

 

[MrPsikick]

Never mind. I figured that last one out myself

It was of course only a matter of embedding it in an if function.

I'm sure there are more ways to do it, but I made it work as following:

=IF(COUNTIF(Sheet2!$A:$A;A1);MIN(IF(Sheet2!$A:$A=A1;Sheet2!$B:$B);"Custom string")

Thanks anyway!