Can't figure out how to do a function/formula

[djbcktt]

I want to display multiple values (that are consolidated in one cell) from an array.

For example, 1st column = State, 2nd column = City, 3rd column = Corporation. I want to see all the States that the Corporation is in in a single cell. I don't want to list a State twice. I can't change the order of the columns.

I can't use a v/hlookup for this because they only work if the lookup value is to the left of the column index number.

Also, I have 0 VBA experience. Can someone help my pitiful soul?

[FDibbins]

Hi and welcome to the forum

I know this is not what you want to hear, but...
Administrative Note:
•We would love to help you with your query, but first, before we can proceed…
•Please see Forum Rule #1 about proper thread titles and adjust accordingly...

To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title

[Pepe Le Mokko]

And while you are at it, please post a sample sheet - Thx

[djbcktt]

Attached is a part of the file I am working on. Thanks.