Search code examples
excelexcel-formulacontains

Get column matching text in excel range

I have lots of columns with values.

I want one specific column (outside that range) to obtain the value from the cells that start with "country: ".

So for example, if that range holds a cell in (let's say) F1 that starts with "country: ", then A1 should ALSO have that value, and so on.

To be more precise, I want something like this.

Thank you!

Solution

  • There are multiple ways of doing this

    Approach 1: using FILTER() and SEARCH()

    =FILTER(G2:K2,NOT(ISERROR(SEARCH("country: ",G2:K2))))

    which returns a #CALC! error when there is no row containing "country: " and a #SPILL! error when more than one row contains "country: " (unless there is enough space to show the whole spill). See example here

    Approach 2: using INDEX(), MATCH() and LEFT()

    =INDEX(G2:K2,MATCH(TRUE,LEFT(G2:K2,9)="country: ",0))

    which returns a #N/A error when there is no row containing "country: " and a the first match when more than one row contains "country: ". See example here

    Approach 3: XLOOKUP() and LEFT()

    =XLOOKUP("country: ",LEFT(G2:K2,9),G2:K2)

    which returns a #N/A error when there is no row containing "country: " and a the first match when more than one row contains "country: ". See example here

    Approach 4: CONCAT(), IF() and LEFT()

    =CONCAT(IF(LEFT(G2:K2,9)="country: ",G2:K2,""))

    which returns an empty string when there is no row containing "country: " and concatenates matches when more than one row contains "country: ". See example here

    Conclusion

    There are many other ways of getting results for your problem. If you could specify whether there can be none or multiple matching columns in a given row, and also how you would like such a scenario to be handled, it would be possible to show you a more precise solution.