First instance based on information from a second table

[Plummet]

Greets!

I apologize for asking something that undoubtedly has been asked before. I can't come up with the right phraseology to ask the right question to get the right answer. I'm sure this will be an easy one for you.

In the attached sheet, I'm looking for a formula that will give me the date of the first instance (I3:I12) from E3:E22. The info in B3:B22 is in another sheet in the real workbook. I need to do this without adding a helper column.

Thanks in advance, I appreciate having Excel Forum as a place to get answers.

[:) Sixthsense :)]

Unable to understand how you arrive the desired result

[Plummet]

To obtain the result in I3, I need a formula that gives me the date for the first time an Alabama city shows up in E3:F22. The info in B3:C22 shows which state each city is in. For Alabama, two cities are listed, Birmingham and Mobile. So I want the result to be the first instance of Birmingham or Mobile. In E3:F22, Mobile shows up first on 6/2/2018.

California would be Sacramento or Salinas. Sacramento shows up on 1/10/2018, which would be the first instance.

In my actual workbook, I'm not dealing with cities, states or dates. If I can get a formula that works for this example spreadsheet, I can make it work for what I need.

Thanks.

[jeffreybrown]

I don't think another explanation is what is needed. I believe we can see what you are clearly saying, it's simply, how do you expect a formula to know that Sacramento and Salinas are associated to California with creating an association.

FWIW, I had the same impression when I first saw this post yesterday, but since I had a feeling it would take a crazy formula, I opted just to watch this post.

Anyway, you said, "without the aide of a helper cell", but sometimes that's exactly what is required.

D3:D22 >> =INDEX(C:C,MATCH(E3,B:B,0))

I3:I12 >> =INDEX(F:F,MATCH(H3,D:D,0))

In total, there are 20 State/City combinations. Not sure that will be easy to do thru a formula. Helper cell gets the job done in no time.

Note: Also, the number of states your currently have is 10. I'm not great with geography, but seems like I learned back in grade school there are a few more states than 10. If the list of states should happen to grow, then so do the State/City combinations. With this in mind, it doesn't seem like you can get away from a helper column too easily.

Of course, I could be completely wrong, but being you posted this over 24 hours ago with little traction, it's quite possible I'm not too far off.

[Plummet]

Thank you very much for your help ... even if it is to tell me what I want to happen can't be done.

I thought that it would be simple for Excel to take H3, find all the matching cities from B3:C22, and then give me the first instance from E3:F22.

OK, I'll just have to accept the fact that this isn't going to be an easy addition to the workbook. I will probably have to do some restructuring.

Thanks again.

[dosydos]

Are you allowed to sort Columns E:F so that the cities in E match up with the cities in column B?

[Plummet]

Negative. That would make things too easy.

[jeffreybrown]

Sorry to give bad news, but creating the connection is the problem.