Match Array Formula

mjfigur
mjfigur used Ask the Experts™
on
I have a spreadsheet attached. On sheet 1 I have a control, control type, and various objectives for the control. On sheet2 I am trying to write some formula that will match the Control Type and scan the array in Sheet1!C2 to locate the Objective ID. Then in Sheet2 place the Control from Sheet1A2 into Sheet2!C2.

 Sample.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
No file attached I'm afraid.:)

Author

Commented:
My apologies. Uploaded the file.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Try this formula and see if this is what you are trying to achieve...

In C2 on Sheet2,
=INDEX(Table1[Control],MATCH(1,INDEX((Table1[Control Type]=TRIM(B2))*(ISNUMBER(SEARCH(A2,Table1[Objective ID]))),),0))

Open in new window

Author

Commented:
Thank you so much. That is very helpful. Just one follow-up question. I uploaded an edited version of the file in which we have multiple F6 Objective ID on Sheet1. Ideally would like it to show both results in a single cell on Sheet2. How would I go about doing this?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
If you are using Excel 2016 or 365, you can use a function called TEXTJOIN to get the desired output otherwise you will need a VBA solution to get the desired output.

In C2 on Sheet2
=TEXTJOIN(", ",TRUE,IF(Table1[Control Type]=TRIM(B2),IF(ISNUMBER(SEARCH(A2,Table1[Objective ID])),Table1[Control])))

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial