Array Formula with partial match.

[bnussbaum1]

I am looking to do an array formula with a partial string match. Does not seem to be working. Are array formulas different than regular index-match formulas with partial string matches?

This works great!
{=IF(CT5="","",(INDEX(testSp23.xlsx!$GN:$GN,MATCH(1,($K5=testSp23.xlsx!$O:$O)*(CT$1=testSp23.xlsx!$FQ:$FQ),0))))}
with testSp23.xlsx referring to another spreadsheet.


However, If I try to add &"*" to the sheet to make CT$1 a partial string match rather than an exact match, it stops working.

{=IFERROR(INDEX(testSp23.xlsx!$FY:$FY,MATCH(1,($K2=testSp23.xlsx!$O:$O)*(CT$1&"*"=testSp23.xlsx!$FQ:$FQ),0)),"")}

Interested in some thoughts here.
I have done partial string matches before, and referencing another workbook is not new either. But Array formulas are new for me.

Credit to:

Tips on Performing an INDEX and MATCH with Two Criteria | Excelchat

Here is how you can use the INDEX and MATCH functions to lookup items with two criteria.

www.got-it.ai

Guidelines and examples of array formulas - Microsoft Support

An array formula (one that spans multiple cells) can do calculations on rows and columns of cells where you might otherwise need to use several formulas. For example, you can count the number of characters that are contained in a range of cells, sum only numbers that meet certain conditions...

support.microsoft.com

Excel Reference Another Sheet | CustomGuide

How to Reference External Worksheets in Excel

www.customguide.com

How to lookup partial string match in Excel?

www.extendoffice.com

for guidance so far. Just trying to put the pieces together.

 

[DanteAmor]

Try:

=IFERROR(INDEX(testSp23.xlsx!$FY:$FY,MATCH(1,($K2=testSp23.xlsx!$O:$O)*(ISNUMBER(SEARCH(CT1&"*",testSp23.xlsx!$FQ:$FQ))),0)),"")

(Is an array formula)

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[bnussbaum1]

Hmmm... Thanks for the idea DanteAmor. IsNumber would likely be needed. Unfortunately still not working. the IfError causes it to just remain blank--so not working yet. I continue to wonder if array formulas can handle a partial string...

Also, wondering if the fact that I am running Office 2019 is part of the problem with this...

 

[DanteAmor]

Works for me:

Dante Amor
	A					F					K																																																																																							CT
1																																																																																																		ct1
2						example					k2
indx

Cell Formulas
Range	Formula
F2	F2	=IFERROR(INDEX(testSp23.xlsx!$FY:$FY,MATCH(1,($K2=testSp23.xlsx!$O:$O)*(ISNUMBER(SEARCH(CT1&"*",testSp23.xlsx!$FQ:$FQ))),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Dante Amor
	A														O																																																																																																																																																														FQ								FY
1
2															k2																																																																																																																																																														ct1 mas								example
testSp23.xlsx

Better this option:

=IFERROR(INDEX(testSp23.xlsx!$FY:$FY,MATCH(1,($K2=testSp23.xlsx!$O:$O)*(CT1=LEFT(testSp23.xlsx!$FQ:$FQ,LEN(CT1))),0)),"")

Press CTRL+SHIFT+ENTER to enter array formulas.