Array Help

[dslaugh]

Hello Excel Community,

I am trying to write a formula to covert text from one database that is in one Column R in Excel Spreadsheet 1 to match the formatting rules for Spreadsheet 2 from a completely different database.

The data in Spreadsheet 1 would be:

Col A Col B
Employee A PLAN Planning Manager
Employee B PLAN Planning Associate
Employee C IT IT Manager
Employee D IT IT Tech
Employee E HR HR Manager
Employee F HR Associate

I need the data converted so that it only has the section they work in:

Range A:

HR
IT
PLAN

If the data in Column B contains the search string from Range A, Return only the value of Range A form each employee.

All I have so far is: {=IF(ISNUMBER(SEARCH($V$2:$V$43,$R$2:$R$138)),$V$2:$V$43,"Test")}

This only returns the first value from Range A

Where the V column is my "Range A" from above, and the R column is the range of employees job title from Spreadsheet 1 above.

Any help you can provide would be appreciated.

Dan

[Melvosh]

It would be helpful if you could upload a sample workbook (with sensitive data replaced / removed). It's far easier to work with actual data. You can upload a workbook by clicking Go Advanced (below the Quick Reply box), then Manage Attachments. Click Browse to find your file, then click Open, then click Upload. Click Close this window, and your file will be attached with your reply.

[dslaugh]

Thank you, I have added the attachment to the original post.

[Fluff13]

Hi & welcome to the board.
How about

Please Login or Register  to view this content.

[63falcondude]

Try this in C2:

=IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW(E$2:E$4)/ISNUMBER(SEARCH(E$2:E$4,B2)),1)),"No Match")

[dslaugh]

Love it. Just curious what the -1 does? It returns everything before the space?

[dslaugh]

This works beautifully. I haven't quite deciphered it yet, but trust me, I will do my best. Thank you both.

[63falcondude]

This works beautifully. I haven't quite deciphered it yet, but trust me, I will do my best. Thank you both.

Happy to help. Thanks for the rep!

You can use Evaluate Formula to help understand what is going on.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Fluff13]

Love it. Just curious what the -1 does? It returns everything before the space?

You're welcome & thanks for the feedback.

The Find will find the space so for "IT IT Tech" it will return 3 & the -1 is there to just get the first 2 characters