Need Help With Wildcard Vlookup

[needhelp221]

I have a list of items that I need to be able to use a vlookup to see if it contains a certain string of text.

For example I need to use the below items to be able to do a look up based off everything before "-4". I know I could do an =len and then use that to do an =left to get rid of the -4 but there is a lot of variability and I'd prefer to use a vlookup and some sort of wildcard.

HSP-39026-4
HSP-39025-4
HSP-39019-4
HSP-39016-4
HSP-39015-4
HSP-39009-4
HSP-39043-4
HSP-39042-4
HSP-38076-4
HSP-38075-4
HSP-38066-4
HSP-38065-4

Any help would be much appreciated.

Thanks

 

[CA_Punit]

Is this what you want??

Book1
	B	C	D	E	F	G
7	HSP-39019	HSP-39019-4			HSP-39026-4
8					HSP-39025-4
9					HSP-39019-4
10					HSP-39016-4
11					HSP-39015-4
12					HSP-39009-4
13					HSP-39043-4
14					HSP-39042-4
15					HSP-38076-4
16					HSP-38075-4
17					HSP-38066-4
18					HSP-38065-4
19
20
21
Sheet1

Cell Formulas
Range	Formula
C7	C7	=VLOOKUP(B7&"*",F:F,1,0)

 

[needhelp221]

I want the reverse- I want the longer number with the dash to return the value that doesn't have the dash.

Thank you for your reply!

 

[CA_Punit]

Can there be a situation like

HSP-39026-4
HSP-39026-5

 

[needhelp221]

Yes there are a lot of variables- could be a -4,-5,-9,-18 etc.

 

[CA_Punit]

Is this what you want

Book1
	F	G	H	I	J	K
7	HSP-39026-4		HSP-39026			HSP-39026
8	HSP-39019-5		HSP-39019			HSP-39019
9	HSP-39019-4		HSP-39019			HSP-39019
10	HSP-39016-4					HSP-39016
11	HSP-39015-4					HSP-39015
12	HSP-39009-4					HSP-39009
13	HSP-39043-4					HSP-39043
14	HSP-39042-4					HSP-39042
15	HSP-38076-4					HSP-38076
16	HSP-38075-4					HSP-38075
17	HSP-38066-4					HSP-38066
18	HSP-38065-4					HSP-38065
Sheet1

Cell Formulas
Range	Formula
H7:H9	H7	=INDEX($K$7:$K$18,AGGREGATE(15,6,SEARCH($K$7:$K$18,F7)*SEQUENCE(COUNTA($K$7:$K$18)),1))

If this is your requirement than there is no point in doing vlookup or any lookup. The lookup value is the result itself. Only point is that one can check whether the value exist in the look-up table