IsError llookup and calling a nearby cell as results.

[pbutcheck]

Hello Excel gurus. I have another wild project that I'm almost able to complete, but stuck on the last part. Previous posts have been resolved (with MUCH appreciation) by using the IsError formula to search an array of values and find matching values. Now the request is similar, with an unfamiliar twist.
We have two sheets. On sheet one there will be hundreds of thousands of values. On Sheet two, we will have a smaller list of important values we need to match up against the larger collection. I am able to write a formula to find the matching values. But NOW, they need one more cell to be displayed. So here's an example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]SHEET ONE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SHEET TWO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Look Up[/TD]
[TD]Resulting Hit[/TD]
[TD]Date Results[/TD]
[TD][/TD]
[TD]Reference List[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1/1/17[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]1/1/17[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]6/15/17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]6/15/17[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5/25/16[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]5/25/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the above table, sheet one would contain hundreds of thousands of values. Sheet two will contain the values we want to find - with a date associated with that value.

Our team wants a way to place a formula in sheet one, pointing to and finding any matches in sheet two. But, they really don't need to "see" the matches, they just need to see the date associated with the match.

I used #2 as an example. Since #2 was found on Sheet 2, the formula should recognize the match (#2 ), and then display the date found NEXT to the match. I displayed the matching concept in a paler color, but it is the date they really want to see.

And maybe this is structured completely wrong; but I couldn't figure out a way to find the matches between two lists, yet formulate the response to show the field NEXT to the matched cell.

I was even toying with the idea of filtering it in reverse: looking up the matching items from sheet one and displaying it on sheet two, and then filtering out the blanks. But before I gave up, I thought I'd check to see if there was a smarter solution.

 

[JoeMo]

in Sheet1, Date Results column, row 2:

=IF(ISNA(MATCH(A2,Sheet2!$A:$A,0)),"",INDEX(Sheet2!$A:$B,MATCH(A2,Sheet2!$A:$A,0),2))

 

[pbutcheck]

This works perfectly! Thank you. Every time I come here, I learn how much I don't know Excel, and how much more this app can do. Deep appreciation.

 

[JoeMo]

This works perfectly! Thank you. Every time I come here, I learn how much I don't know Excel, and how much more this app can do. Deep appreciation.

You are welcome - thanks for the reply.