Help with FILTER function

[jdellasala]

I posted an item regarding Mr.E's Excel Formula To Extract Country From Phone Number - 2370 which I loved here today. I used a 365 formula to lookup Country Codes like this:

CountryCodeLookups.xlsx
	A	B
1	+447594361587	United Kingdom
2	+41769482539	Switzerland
3	+35056369249	Gibraltar
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LET(Ph,SUBSTITUTE(A1,"+",""), IFNA(XLOOKUP(LEFT(Ph,6)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,5)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,4)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,3)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,2)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,2)+0,CountryCodes[Code],CountryCodes[Country]), IFNA(XLOOKUP(LEFT(Ph,1)+0,CountryCodes[Code],CountryCodes[Country]), "#ERROR!"))))))))

which references a Table of codes.
Anyway, the formula works fine, but I thought I might be able to streamline it a bit more using SEQUENCE function like this:

CountryCodeLookups.xlsx
	D	E
3	+447594361587
4
5
6
7		United Kingdom
Sheet1

Cell Formulas
Range	Formula
E3:E8	E3	=LET(Ph,SUBSTITUTE(D3,"+",""),IFNA(XLOOKUP(LEFT(Ph,SEQUENCE(6,,6,-1))+0,CountryCodes[Code],CountryCodes[Country]),""))
Dynamic array formulas.

I get the answer, but I need to filter it to get only the item with a value. I tried this:

CountryCodeLookups.xlsx
	D	E
3	+447594361587	#VALUE!
Sheet1

Cell Formulas
Range	Formula
E3	E3	=LET(Ph,SUBSTITUTE(D3,"+",""),FILTER(IFNA(XLOOKUP(LEFT(Ph,SEQUENCE(6,,6,-1))+0,CountryCodes[Code],CountryCodes[Country]),""),"<>"))

but obviously either FILTER won't work or I've got it wrong. Any help?

 

[RoryA]

You could use LOOKUP:

=LET(Ph,SUBSTITUTE(D3,"+",""),LOOKUP(REPT("Z",255),XLOOKUP(LEFT(Ph,SEQUENCE(6,,6,-1))+0,CountryCodes[Code],CountryCodes[Country])))

Your FILTER syntax appears to be confused with COUNTIF. FILTER requires an array of criteria.

 

[jdellasala]

You could use LOOKUP:

=LET(Ph,SUBSTITUTE(D3,"+",""),LOOKUP(REPT("Z",255),XLOOKUP(LEFT(Ph,SEQUENCE(6,,6,-1))+0,CountryCodes[Code],CountryCodes[Country])))

Your FILTER syntax appears to be confused with COUNTIF. FILTER requires an array of criteria.

I don't follow. This still results in a #VALUE! error:

=LET(Ph,SUBSTITUTE(D3,"+",""),FILTER(IFNA(XLOOKUP(LEFT(Ph,SEQUENCE(6,,6,-1))+0,CountryCodes[Code],CountryCodes[Country]),""),"="&CountryCodes[Country]))

The array FILTER is looking at are the 6 results of the XLOOKUP. Don't follow where COUNTIF would fit in.
I'm trying to condense the 6 XLOOKUPS in the beginning into one XLOOKUP, but don't want all 6 results. Need a DROPIFS function!

 

[RoryA]

It works fine for me.

I wasn't saying you should use COUNTIF. I was saying that the syntax you are using for your FILTER criterion is what you would use for COUNTIF, not what you'd use for FILTER. FILTER needs an array of True/False values, not a single value.

 

[jdellasala]

It works fine for me.

I wasn't saying you should use COUNTIF. I was saying that the syntax you are using for your FILTER criterion is what you would use for COUNTIF, not what you'd use for FILTER. FILTER needs an array of True/False values, not a single value.

I understood what you meant, but it didn't make sense. I was looking for help, not obscure hints.
No check for you!

 

[RoryA]

I gave you a working formula and explained why your syntax was wrong.

I'll leave you be.

 

[jdellasala]

I gave you a working formula and explained why your syntax was wrong.

I'll leave you be.

OMG! My bad!!! I just moved and had the cable guy here and totally missed the formula in your first post. I couldn't understand why you hadn't posted a solution. I should have known better.
That said, I'm not sure how or why it works, but I'll investigate further.
THANK YOU!