Search file name in a cell and vlookup using partial match

[kkexcel1]

Hi everyone! Needing some help building a formula - I'm at a complete loss at how to do this!

I'm trying to find the file name base (A17:A21) in each cell in A2:A13 and return the proposal number in B17:B21.

ex: Cell B2 should show "1", cell B8 should return "4"

Thanks so much!

 

[AlanY]

try this in B2 and copy down

=LOOKUP(2,1/ISNUMBER(SEARCH($A$17:$A$21,A2)),$B$17:$B$21)

 

[kkexcel1]

@AlanY - works perfect! thank you so much!

 

[AlanY]

@AlanY - works perfect! thank you so much!

You’re welcome