VLOOKUP with use of wildcard and CONCATENATE

Question

In F5 I have an ID. E.g 1358. In F6 I have a further value which I add onto the ID string. E.g 2.

In my 'pieces' table are a bunch of Level ID. E.g

135821 135822 135823 135824

=VLOOKUP("*"(CONCATENATE(F5,F6))"*",pieces[[Level ID]:[Move Recorded]],2,FALSE).

I want to be able to find all the entries that are 13582* and list them below. I'm not sure how to do this with multiple entries and if VLOOKUP is even the best way to go. Hope I've made myself clear, any help appreciated!

Answer 1

Vlookup will only return the first, you need to use something like Small or Aggregate to get the list.

If you have 2010 or later than use this formula:

=INDEX(peices[Move Recorded],MATCH(AGGREGATE(15,6,peices[Level ID]/(ISNUMBER(SEARCH("*" & $F$5 & $F$6 & "*",peices[Level ID]))),ROW(1:1)),peices[Level ID],0))

If you have 2007 or earlier than you will need to use this array formula:

=INDEX(peices[Move Recorded],MATCH(SMALL(IF(ISNUMBER(SEARCH("*" & $F$5 &$F$6& "*",peices[Level ID])),peices[Level ID]),ROW(1:1)),peices[Level ID],0))

Being an array it will need to be confirmed with Ctrl-Shift-Enter when exiting edit mode.