I am using the below formula to filter through a set of numbers between two sheets to find out the ID numbers of various employees but it crashes when a letter is accidentally included in the search range.
How do I force FILTER to account for text and numbers?
=TEXTJOIN(", ",,FILTER(A$2:A$6,--RIGHT(B$2:B$6,4)=D2,"Nothing"))
Sample Data:
Sheet 1
| Name | 12 Digit ID |
|---|---|
| John | sample111TypoHere1344 |
| Jake | 123456782567 |
| Eliza | 558456782567 |
| Tony | 558456789911 |
| Jane | 564231846155 |
Sheet 2
| Last Digit ID's | Guess for Employee's Full ID From Searching Sheet 1 |
|---|---|
| 2567 | |
| 9911 | |
| 4555 | |
| 1234 |
Expected output:
Sheet 2
| Last Digit ID's | Guess for Employee's Full ID From Searching Sheet 1 |
|---|---|
| 2567 | Jake or Eliza |
| 9911 | Tony |
| 4555 | Typed wrong or Does Not Exist |
| 1234 | Typed wrong or Does Not Exist |
Actual output:
| Last Digit ID's | Guess for Employee's Full ID From Searching Sheet 1 |
|---|---|
| 2567 | #VALUE! |
| 9911 | #VALUE! |
| 4555 | #VALUE! |
| 1234 | #VALUE! |
I tried this long line using an IFERROR command that accounts for "Text" and "Value" but it also failed:
=IFERROR(TEXTJOIN(", ",,FILTER(H$118:H$120,--RIGHT(I$118:I$120,4)=TEXT(D2,0),"Error 0")), TEXTJOIN(", ",,FILTER(H$118:H$120,--RIGHT(I$118:I$120,4)=VALUE(K124),"Error 1")))
Actual output:
| Last Digit ID's | Guess for Employee's Full ID From Searching Sheet 1 |
|---|---|
| 2567 | Error 0 |
| 9911 | Error 0 |
| 4555 | Error 0 |
| 1234 | Error 0 |
CodePudding user response：
If there are letters in the last four characters --RIGHT(B$2:B$6,4) will error. Instead change the search criteria to a string by concatenating &""
=TEXTJOIN(" or ",,FILTER(A$2:A$6,RIGHT(B$2:B$6,4)=D2&"","Typed wrong or Does Not Exist"))