IsError llookup and calling a nearby cell as results.

pbutcheck

New Member
Joined
Jul 22, 2015
Messages
14
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:

SHEET ONESHEET TWO
Look UpResulting HitDate ResultsReference ListDate
121/1/17
221/1/1736/15/17
36/15/1755/25/16
4
55/25/16

<tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
in Sheet1, Date Results column, row 2:

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

pbutcheck

New Member
Joined
Jul 22, 2015
Messages
14
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,502
Messages
5,602,041
Members
414,497
Latest member
guitarmanz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top