Trouble with XMATCH using multiple criteria and a wildcard

[mpenley]

I have a dynamic spreadsheet where formula input data is designed to change based on user inputs. Which leads to many complicated index matches pulling data from source data. Below is the formula i am having trouble with. It is an INDEX XMATCH XMATCH With multiple criteria and a wildcard.

=INDEX('Building Permits Annual Data'!$A$1:$N$8477,IFNA(XMATCH(1,($B$29='Building Permits Annual Data'!$A$1:$A$8477)*(I$29='Building Permits Annual Data'!$B$1:$B$8477)),XMATCH(1,($B$39&"*"='Building Permits Annual Data'!$A$1:$A$8477)*(I$29='Building Permits Annual Data'!$B$1:$B$8477),2)),$G$37)

Where B29 is a name of a Metropolitan area, B39 is the first City (major city) in a metropolitan area if the source data doesn't have a direct match to B29. The IFNA portion of the formula is designed to select B39 and do a wild card search based on just the first city, if B29 cannot be found. I29 is the year that also helps to define the row in INDEX XMATCH XMATCH (multiple criteria). 

I'm open to any suggestions or better ways to do this. The last image is from the source data that has around 250 Metropolitan areas that I am searching through. 

[#2 · Accepted solution]

@mpenley 

But you have no wildcard for the XMATCH here. Your formula is like

XMATCH(1, condition, ...

If use wildcard it shall be where 1 is. In condition you compare texts like "Austin*" = "Austin Something" and it's never returns TRUE since here wildcard doesn't work. 

As variant you may use ISNUMBER(SEARCH(... in condition with exact match for XMATCH.

[#3]

@mpenley 

THAT is one ambitious workbook. And you clearly are not a novice Excel user. So rather than personally try to diagnose, I'm going to make the suggestion that, just in case you haven't become aware of them, Excel late last year issued some new Dynamic Array functions--notably SORT, UNIQUE, FILTER--that might be able to help in your situation. FILTER in particular works well with multiple criteria without the need for multiple levels of nesting.  I was not aware of them until I stumbled across this video, and they've been life-changing in many ways. Well, spreadsheet-changing at least.

Here's a YouTube video that serves as a good intro to those functions and their applications. https://www.youtube.com/watch?v=9I9DtFOVPIg

[#4]

@mathetes Thank you! I have been aware of those functions and they do come in handy. Although I am excited to watch the clip you posted below. I am sure there is still plenty to learn and I am always striving to make anything I work on simpler and more elegant.

[#5]

@Sergei Baklan you nailed it with the isnumber(search! That did the trick. Thank you for your help. 

[#6]

@mpenley , you are welcome