Dynamic INDEX/MATCH or LOOKUP that can scan 2 or 3 columns and return dynamic list of results in correct cascading order

[Eduard_Stoo]

Hi All -

No problems at all if I'm indexing 1 x column - but I have a sheet with 2 or three columns where I need to find wildcards (i.e. text and the appropriate symbol, or the symbols on their own) and return a dynamic list of the results, and also in order as per the arrows in the below mock-up? i.e. column 1 first, then back to the top of column 2 and search down. Results cannot be combined or out of order.

I'm sure I managed this many moons ago, but can't now remember how I cracked it as it's been a long time!

Thanks in advance...

 

[Cubist]

Try:

=TOCOL(XLOOKUP(B2:C20,I2:I4,I2:I4),2,1)

 

[Eduard_Stoo]

Thanks very much CUBIST - I've never really used the TOCOL function before, but it's proving useful.

If I could be so bold, as a follow-up, I'm trying to get the type of machinery check (that was flagged with one of the three symbols) to populate in the column next to your formula - obviously this involves some repetition, as (as per the below example) there are 2 x bearings checks and 2 x lubrication checks taking place. D; you know if there's a way for the formula to return the corresponding check, depending on which row the symbol was flagged against please? I'm not sure if the same formula could work in this regard, thanks so much

 

[Cubist]

Replace the prior formula with this. It'll return both columns.

=LET(x,XLOOKUP(B2:C20,I2:I4,I2:I4),HSTACK(TOCOL(x,2,1),TOCOL(IFS(1-ISNA(x),A2:A20),2,1)))

 

[kvsrinivasamurthy]

Post deleted

 

[kvsrinivasamurthy]

Try.

=LET(a,B2:C20,b,IF(COUNTIF(I2:I4,a)>0,a,""),c,TOCOL(b,2,1),FILTER(c,c<>""))

 

[Eduard_Stoo]

Thanks both. Working like a charm!