VLOOKUP match on first x values or ignore last x values - partial match

test.xlsxHey guys,

I have full part numbers that I need matched with part number families. 123-456-789 needs to match 123-456-xxx for example. Is there a way to do that? =VLOOKUP(A3,C3:D6,2) obviously doesn't work Thanks!

A B C E
Part number Yes or No Part family
123-456-789 #N/A 123-456-xxx yes
123-456-456 234-567-xxx yes
123-456-888 345-678-xxx no
234-567-898
234-567-888
234-567-882
345-678-111
345-678-555
345-678-888

What about

Please Login or Register  to view this content.

Hi bobtu, try this array formula (ctrl+shift+enter) in B3 and copy down till B11.

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Array Formula:

=INDEX($D$3:$D$5,MATCH(LEFT(A3,LEN(A3)-FIND("-",A3,1)),LEFT($C$3:$C$5,LEN($C$3:$C$5)-FIND("-",$C$3:$C$5,1)),0))

or check the file

Azumi