Partial cell Match

[chadlaw32]

I am trying to right a formula that will look up the firs set of values in a cell where there are numbers separated by a dash below is an example of the data. I want to look up the 301 or 024. Any ideas?

301-513410

303-545826

024-562515

303-556837

027-499338

303-557957

313-518514

<colgroup><col></colgroup><tbody>
</tbody>

 

[alansidman]

Here is a VBA solution to try

Option Explicit


Sub chadlaw()
    Dim lr As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row    'change the range to the column you are testing
    For i = 2 To lr    'assumes you have a header in the column
        If InStr(Range("A" & i), "301-") Or InStr(Range("A" & i), "024-") Then
            MsgBox "Found"
            Exit Sub
        End If
    Next i
End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Aladin Akyurek]

What is the value to look up and in which table that value should be looked up?

 

[chadlaw32]

What i want is a vlookup for the values before the dash the numbers in he cell are 678-456235 and i want to reference the 678

 

[Aladin Akyurek]

What i want is a vlookup for the values before the dash the numbers in he cell are 678-456235 and i want to reference the 678

=VLOOKUP(X2&"*",Table,1,0)


where X2 = 678 and Table is the range housing those 7 values you forwarded in your initial post.

 

[chadlaw32]

The sheet has account numbers in column B2 and those are 678-456789 and 123-987654 where 678 or 123 reference a franchise i want to run a vlookup on B2 for a table that list all the franchise.

 

[Aladin Akyurek]

The sheet has account numbers in column B2 and those are 678-456789 and 123-987654 where 678 or 123 reference a franchise i want to run a vlookup on B2 for a table that list all the franchise.

I don't understand what you mean by franchise... Is it not just this what you need?

Book1
	B	C	D	E
1
2	301-513410		678	678-456789
3	303-545826		123	123-987654
4	024-562515
5	303-556837
6	027-499338
7	303-557957
8	313-518514
9	678-456789
10	123-987654
11
Sheet1

E2 copied down:

=VLOOKUP(D2&"*",B:B,1,0)

 

[chadlaw32]

Get rid of column D I want to look up part of column B. 301 an 303 and 024 on your table where i have another table that says 301 is city A and 303 is city b and 024 is city C.

 

[KolGuyXcel]

Try

=VLOOKUP(VALUE(LEFT(B2,3)),City_Names_Table,2,0)

where City_Names_Table is the 2(at least)-column table that you're referring to as "another table" in post # 8!

EDIT: because missed the 024

either
=VLOOKUP(VALUE(LEFT(B2,3)),City_Names_Table,2,0)
or
=IF(VALUE(LEFT(B4,3))<100,VLOOKUP(TEXT(LEFT(B4,3),"000"),City_Names_Table,2,0),VLOOKUP(VALUE(LEFT(B4,3)),City_Names_Table,2,0))

 

[chadlaw32]

Perfect thank you!!