Return Leftmost 7 characters if cell contains certain text somewhere in the cell

[Kurtin]

I've tried google, and my own knowledge, but nothing is seeming to work. (I can't get XL2BB to work for this)
I want to Find "EXTRA" in the "Invoice" Column, and return the Invoice # if true.

I thought I could use XLookup, but it's not working, nor is the IF(Countif(cell,*""*),Y,N or anything I try. I know it's probably the simplest answer... but of course I can't seem to find it

I want the formula to be in sheet 2 and pull from sheet 1.
I tried =XLOOKUP("Extra",Table2[@[Invoice '#]],LEFT(Table2[@[Invoice '#]],7),"",0)

Anyways, any help is greatly appreciated! TIA

Sheet 1

Sheet 2

 

[Fluff]

How about

=XLOOKUP("*Extra*",Table2[@[Invoice '#]],LEFT(Table2[@[Invoice '#]],7),"",2)

 

[Kurtin]

How about

=XLOOKUP("*Extra*",Table2[@[Invoice '#]],LEFT(Table2[@[Invoice '#]],7),"",2)

nope, nothing unfortunately...

 

[Fluff]

Did you change the final argument from 0 to 2?

 

[Kurtin]

Did you change the final argument from 0 to 2?

Yes, it's not returning anything, the cell is blank, no error

 

[Fluff]

In that case can you post some sample data.

 

[Kurtin]

Okay, so I changed one thing, =XLOOKUP("*Extra*",Table2[@[Invoice '#]],LEFT(Table2[@[Invoice '#]],7),"",2) (removed the ] in red) and now it's pulling information, but, it's repeating itself as far as I will pull the table down

this is where I want to look at "Extra" and pull the Invoice # from (I tried putting "extra" on 1234568, but the result below was still the same, it wouldnt pull 1234568)

this is where I want to pull to. but it's being funky

 

[Fluff]

it's repeating itself as far as I will pull the table down

That's exactly what xlookup does. It finds the 1st value that matches.

 

[Kurtin]

Okay, then I shouldnt be using Xlookup, how do I get it to return all the different #'s that have "Extra" noted

 

[Fluff]

How about

=FILTER(LEFT(Table2[Invoice '#],7),RIGHT(Table2[Invoice '#],6)="Extra)")

But do not put this in a table.