Excel: In what order a word appear

Question

I have this case right here. The table below is what I expect, but it's originally without a formula.

Info for the pic: On the green cell, i'd like to find two criteria from two different cells: "Car" & "Ford", in what line are they?

The blue cell is the second example. It is the same but different criteria. So I want to do this to build a product number with my own formula that I already built, except this last things.

My question is: What formula that can do that. I did a full day research on the internet, google and youtube. But I don't get and answer. And I'm not an expert in a excel. I only did and googling all the things I did in excel. So please help me find out how.

Thank You

Answer 1

In this example the formula shown is entered in E2 and filled down.

Note this is an array formula, so use Ctrl+Shift+Enter to enter it.

Answer 2

I'd personally suggest you look into Pivot Tables. They do exactly what you want and probably more.

https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-A9A84538-BFE9-40A9-A8E9-F99134456576