INDEX / SMALL / (match?) partial lookups, multiple criteria

[Bronzey]

Having no luck getting my head around the order or events and hoping it's easier than I'm making it out.

Two columns- column A is a list of names; column B is either a numerical entry or "tbd" for 'to be decided.

Want to put out a list of names where the first two characters can be the "tb" in "tbd" or the numbers 12 (no matter if the number is 12.5, or 12000).

I've only gotten the 'tbd' working so far but hoping combine both into a single list in a new column (can't use the combined comma'd list as not on Excel 365, only on premise Excel 2016).

=IF(ISERROR(INDEX($A$2:$A$11,SMALL(IF("TBD"=$B$2:$B$11,ROW($B$2:$B$11)-ROW($B$2)+1),ROW(1:1)))),"",INDEX($A$2:$A$11,SMALL(IF("TBD"=$B$2:$B$11,ROW($B$2:$B$11)-ROW($B$2)+1),ROW(1:1))))

Is there an elegant way to use LEFT(x,2) to grab the first two digits in an 'OR' statement so both "TBD" and 12* can then be pulled into a name list in a new column?

 

[DanteAmor]

Try this:

Excel Workbook
	A	B	C
1	NAME	DATA	NAMES
2	Ana	TBD	Ana
3	Frank	text	John
4	John	12000	Ron
5	Ron	TBD	Lisa
6	Bart	text	Jan
7	Lisa	12.5
8	Hom	text
9	Jan	TBD
10	Gab	text
11	Est	text
12	West	text
Hoja7

 

[DanteAmor]

I put a correction:


{=IFERROR(INDEX($A$1:$A$12,SMALL(IF(ISNUMBER(SEARCH({"TB","12"},LEFT($B$2:$B$12,2))), ROW($B$2:$B$12)), ROW()-1)),"")}

Note: Remember it is an arrayformula

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$100,SMALL(IF(ISNUMBER(MATCH(LEFT($B$2:$B$1100,2),{"TB","12"},0)),ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($1:1))),"")

 

[Bronzey]

Slick beyond measure - thanks so much

 

[DanteAmor]

You're welcome