Forum Discussion

Copper Contributor

Sep 17, 2020

Solved

Trouble with XMATCH using multiple criteria and a wildcard

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 a...

BEA Aggregator Auto Versions 2 - Copy.xlsx3.7 MB

Formulas and Functions

SergeiBaklan
Sep 17, 2020
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.

mathetes

Gold Contributor

Sep 17, 2020

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

mpenley

Copper Contributor

Sep 17, 2020

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.