Is there a way for <search-value> in the MATCH function to be ANY non-blank value?

BarryO

I'd like to be able to search a column to find ANY first non-blank value not just the one corresponding to the first argument in the MATCH function. A wild card basically for <search_value>.

BarryO

Lucas, thank you for taking your time to respond. If I understand correctly what you've suggested I'll wind up with multiple values; text in my case. I just want the FIRST any non-blank (text) value from the selected range- as example Column A in your response. If I do join-collect I'd still need to parse it further to grab the first non-blank text from the (potentially) multiple values returned. I tried placing a wildcard * or "*" as the first argument of the MATCH statement along with a 0 for the third MATCH argument (search_type) without success. I'd be nice if MATCH had additional search types e.g. 2=Any (first-non blank) text, 3=Any (first-non blank) numeric and so on.

Lucas Rayala

Hi @BarryO, to clarify, you have two columns, A and B. Column A contains a value you wish to return. Column B is a criteria column, and you want the criteria to be "blank" values?

Basic format:

=JOIN(COLLECT({Column A}, {Column B}, ""), " ")

You can put anything in the " " (the second clause of the JOIN statement) -- this will be added between your return values.

BarryO

Lucas, thank you for taking your time to respond. If I understand correctly what you've suggested I'll wind up with multiple values; text in my case. I just want the FIRST any non-blank (text) value from the selected range- as example Column A in your response. If I do join-collect I'd still need to parse it further to grab the first non-blank text from the (potentially) multiple values returned. I tried placing a wildcard * or "*" as the first argument of the MATCH statement along with a 0 for the third MATCH argument (search_type) without success. I'd be nice if MATCH had additional search types e.g. 2=Any (first-non blank) text, 3=Any (first-non blank) numeric and so on.

BarryO

Lucas your answer did help. Using your concept I was able to get what I needed with the following:

=LEFT(JOIN(COLLECT([WC-Complete]:[WC-Complete], [Room Number]:[Room Number], [Room Number]@row)), 2)

Not a 100% ideal but it gives me what I need. I get the first two characters from the JOIN, can't go more as one of the values returned could ne "No" and grabbing more characters than just two characters could give me a concatenated answer of more than just the first value.

Still I'd like to see MATCH beef-ed up a bit. Again thank you for your time.