I have an XLOOKUP formula in cell E2 that looks at cell B2, searches for that entry in Table A and returns its result in cell E2.
I have "wrapped" the search cell in a wildcard - "*"&E2&"*" - so that when I copy the formula down it would take into account any difference in the cell in column B, as in cell B3.
Cell B3 is not an exact match in Table A - there is a difference in the year between the two.
Should this formula work and if it can, why won't my example work?
For clarity I asked a similar question on another site, but I didn't specify the need for wildcards as I have here.
I have "wrapped" the search cell in a wildcard - "*"&E2&"*" - so that when I copy the formula down it would take into account any difference in the cell in column B, as in cell B3.
Cell B3 is not an exact match in Table A - there is a difference in the year between the two.
Should this formula work and if it can, why won't my example work?
| XLOOKUP_ WIldcard Query.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | E | F | G | H | I | J | ||||
| 1 | Index No. | Application Name | Version | Discovered Installs | Licenses Held | Table A | Result | |||||
| 2 | 1 | Adobe Acrobat DC | 21.007.20099 | 7 | 8 | Adobe Acrobat DC | 7 | |||||
| 3 | 2 | Adobe After Effects 2021 | 18.4.1 | #N/A | 4 | Adobe Acrobat DC (64-bit) | 168 | |||||
| 4 | 3 | PLSQL Developer 13 (32 bit) | 13.0.6.1911 | 1 | 5 | Adobe Acrobat Reader DC | 1954 | |||||
| 5 | 4 | Apro Banking Gateway | 0 | 9 | 10 | Adobe After Effects 2022 | 1 | |||||
| 6 | 5 | Articulate 360 | 1.56.26145.0 | 1 | 1 | Articulate 360 | 1 | |||||
| 7 | 6 | ASG-Remote Desktop 2018 | 11.0.6169.1 | 15 | 20 | ASG-Remote Desktop 2018 | 15 | |||||
| 8 | 7 | Jira Core (Server) | 0 | #N/A | 100 | PLSQL Developer 13 (32 bit) | 1 | |||||
| 9 | 8 | Jira Software (Server) | 0 | 4 | 100 | Apro Banking Gateway | 9 | |||||
Restricted DB Master | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:E3 | E2 | =XLOOKUP("*"&B2&"*",I2:I8,J2:J8,,2) |
| E4 | E4 | =XLOOKUP("*"&B4&"*",I4:I9,J4:J9,,2) |
| E5 | E5 | =XLOOKUP("*"&B5&"*",I5:I9,J5:J9,,2) |
| E6 | E6 | =XLOOKUP("*"&B6&"*",I6:I9,J6:J9,,2) |
| E7 | E7 | =XLOOKUP("*"&B7&"*",I7:I9,J7:J9,,2) |
| E8 | E8 | =XLOOKUP("*"&B8&"*",I8:I9,J8:J9,,2) |
For clarity I asked a similar question on another site, but I didn't specify the need for wildcards as I have here.
