XLOOKUP prioritise values that starts with certain text

[ausswe]

Hi,
I've been playing around with various alternative XLOOKUP formulas and INDEX/MATCH to lookup values in a table and return a match - where I want matches that starts with "HTJ" to be prioritised.
In other words, if a value is found and starts with "HTJ xxxxx" then use that, if not found take another match (I hope that explanation makes sence....).

Below is the XLOOKUP formula that I have used and that works, but it grabs the first entry in the table SoL[Resurs], but here I'm trying to get it to prioritise if an entry in SoL[Resurs] starts with "HTJ*".
I have tried different alternatives with wildcards etc but can't get it to work. Is it possible with XLOOKUP?

=XLOOKUP(INDIRECT("$I3:$I"&$P$1)&1;SoL[PID]&--((SoL[Resurs]<>"")*(SoL[Verkställda timmar]<>""));SoL[Resurs])

Thanks in advance

 

[dreid1011]

You can achieve this if you sort the list with "HTJ*" entries at the beginning or end of the list and set the search mode to account for that so long as it looks at the "HTJ*" entries first. (First to Last, Last to First)

Book1
	A	B
1	Green	111
2
3	Yellow	56
4	Red	49
5	Green	50
6	Blue	53
7	HTJ Red	100
8	HTJ Green	111
9	HTJ Blue	105
Sheet5

Cell Formulas
Range	Formula
B1	B1	=XLOOKUP("*"&A1,A3:A9,B3:B9,"",2,-1)

 

[ausswe]

Thansk dreid1011, unfortunately that might not be an efficient option as the data it goes through contains 10,000s of entries every month and there are values in the list with entries that starts A-G as well as I-Z so sorting the list would mean that H ends up in the middle of each month anyway.

 

[dreid1011]

Okay, give this a try then:

Book1 (version 1).xlsb
	A	B
1	Blue	105
2
3	Blue	53
4	Green	50
5	HTJ Blue	105
6	HTJ Green	111
7	HTJ Red	100
8	Red	49
9	Yellow	56
Sheet5

Cell Formulas
Range	Formula
B1	B1	=LET(x,XLOOKUP("*"&A1,FILTER(A3:A9,LEFT(A3:A9,3)="HTJ"),FILTER(B3:B9,LEFT(A3:A9,3)="HTJ"),"",2),IF(x<>"",x,XLOOKUP(A1,FILTER(A3:A9,LEFT(A3:A9,3)<>"HTJ"),FILTER(B3:B9,LEFT(A3:A9,3)<>"HTJ"),"")))

 

[ausswe]

Okay, give this a try then:

Book1 (version 1).xlsb
	A	B
1	Blue	105
2
3	Blue	53
4	Green	50
5	HTJ Blue	105
6	HTJ Green	111
7	HTJ Red	100
8	Red	49
9	Yellow	56
Sheet5

Cell Formulas
Range	Formula
B1	B1	=LET(x,XLOOKUP("*"&A1,FILTER(A3:A9,LEFT(A3:A9,3)="HTJ"),FILTER(B3:B9,LEFT(A3:A9,3)="HTJ"),"",2),IF(x<>"",x,XLOOKUP(A1,FILTER(A3:A9,LEFT(A3:A9,3)<>"HTJ"),FILTER(B3:B9,LEFT(A3:A9,3)<>"HTJ"),"")))

Hi,
I can't quite get my head around that formula. Since I'm not allowed to install XL2BB on my work computer I have tried to replicate the scenario in the HTML table below where:
- The "LOOKUP TABLE" contains a large set of ID numbers (PID), I have included the previous formula below "Desired result" which performed a lookup in a separate data table to match the id number IF SoL[Hrs] and SoL[Resurs] contained values and the result would be the first available result (in this scenario "KTB Lant"). However I need to prioritise results that starts with HTJ so in other words, the desired result should be "HTJ Lant" instead. Hope that makes more sense.

(Update: adding an HTML table didn't work so I made an Excel online sample instead: XLOOKUP with priority.xlsx)

<style>

            table td, table th {

                font-size: smaller;

                border: 1px solid black;

                line-height: 1.4;

                padding: 10px 10px;

            }

        </style><table><tbody><tr><th>LOOKUP TABLE</th><th></th><th></th><th>SEPARATE DATA TABLE</th><th></th><th></th></tr><tr><td>PID (LOCATE)</td><td>DESIRED RESULT</td><td></td><td>SoL[PID] (EXPORTED DATA)</td><td>SoL[Resurs]</td><td>SoL[Hrs]</td></tr><tr><td>60D90E532E7A1FF8C125768E003B4F69</td><td>XLOOKUP(A2&amp;1;SoL[PID]&amp;--((SoL[Resurs]&lt;&gt;"")*(SoL[Hrs]&lt;&gt;""));SoL[Resurs])</td><td></td><td>3040A06639974C7CC125768E003B5A78</td><td></td><td></td></tr><tr><td>52CF6043C901CAC4C125768E003B2E10</td><td> (above is the previous formula)</td><td></td><td>8541DCF048BCF460C125768E003B281F</td><td></td><td></td></tr><tr><td>E4B3FEF881C73D3CC125768E003B2743</td><td></td><td></td><td>1119BD4D912CC3B7C125768E003B0A3F</td><td></td><td></td></tr><tr><td>C3C12086E65EEAD5C125768E003B2221</td><td></td><td></td><td>52CF6043C901CAC4C125768E003B2E10</td><td></td><td></td></tr><tr><td>4C3926200EEBB735C125768E003B2A2B</td><td></td><td></td><td>60D90E532E7A1FF8C125768E003B4F69</td><td></td><td></td></tr><tr><td><strong>852D31D5C1B45961C125768E003B0044</strong></td><td>HTJ Lant</td><td></td><td><strong>852D31D5C1B45961C125768E003B0044</strong></td><td>KTB Lant</td><td>35</td></tr><tr><td>8541DCF048BCF460C125768E003B281F</td><td></td><td></td><td>7E1DA6144C669F90C125768E003B3B8E</td><td></td><td></td></tr><tr><td>1119BD4D912CC3B7C125768E003B0A3F</td><td></td><td></td><td><strong>852D31D5C1B45961C125768E003B0044</strong></td><td>HTJ Lant</td><td>151</td></tr><tr><td>52CF6043C901CAC4C125768E003B2E10</td><td></td><td></td><td>C3C12086E65EEAD5C125768E003B2221</td><td></td><td></td></tr><tr><td>60D90E532E7A1FF8C125768E003B4F69</td><td></td><td></td><td><strong>852D31D5C1B45961C125768E003B0044</strong></td><td>ANHAV Lant</td><td>27</td></tr></tbody></table>

 

[dreid1011]

The concept of this formula is this:

=LET(x,XLOOKUP("*"&A1,FILTER(A3:A9,LEFT(A3:A9,3)="HTJ"),FILTER(B3:B9,LEFT(A3:A9,3)="HTJ"),"",2),IF(x<>"",x,XLOOKUP(A1,FILTER(A3:A9,LEFT(A3:A9,3)<>"HTJ"),FILTER(B3:B9,LEFT(A3:A9,3)<>"HTJ"),"")))

LET allows us to define variables to represent other values or functions to help make a long formula shorter and more readable, especially if part of it is repeated. In this case "x".

x = XLOOKUP("*"&A1,FILTER(A3:A9,LEFT(A3:A9,3)="HTJ"),FILTER(B3:B9,LEFT(A3:A9,3)="HTJ"),"",2)

This is the working part of the formula:

IF(x<>"",x,XLOOKUP(A1,FILTER(A3:A9,LEFT(A3:A9,3)<>"HTJ"),FILTER(B3:B9,LEFT(A3:A9,3)<>"HTJ"),""))

It is starting with the first lookup (represented by "x"), which filters the data for all entries starting with "HTJ". If the lookup value is not found, then it proceeds to the second lookup which filters the data for values not starting with "HTJ".

If the value is not found in either lookup, then it returns a blank.



Do all the values in the source data have a letter prefix?

Edit: Looking at your sample, you are looking up the longer value and your return value has the prefix? If "HTJ" does not exist, which value should be returned?

 

[dreid1011]

Does this look like what you want?

Book1 (version 1) 1-25-2024.xlsx
	A	B	C
1	SoL[PID]	SoL[Resurs]	SoL[Hrs]
2	3040A06639974C7CC125768E003B5A78
3	8541DCF048BCF460C125768E003B281F
4	1119BD4D912CC3B7C125768E003B0A3F
5	52CF6043C901CAC4C125768E003B2E10
6	60D90E532E7A1FF8C125768E003B4F69
7	852D31D5C1B45961C125768E003B0044	KTB Lant	35
8	7E1DA6144C669F90C125768E003B3B8E
9	852D31D5C1B45961C125768E003B0044	HTJ Lant	151
10	C3C12086E65EEAD5C125768E003B2221
11	852D31D5C1B45961C125768E003B0044	ANHAV Lant	27
12
13
14	Lookup Value	Desired Result?
15	852D31D5C1B45961C125768E003B0044	HTJ Lant	151
Sheet7

Cell Formulas
Range	Formula
B15:C15	B15	=LET(x,FILTER(B2:C11,A2:A11=A15),FILTER(x,LEFT(CHOOSECOLS(x,1),3)="HTJ",""))
Dynamic array formulas.