Conditional statement

Martha

I propose you create anothe lookup table:

AA 2
DL 3
CO 4
etc

Then change your formula to:

=VLOOKUP(B1,MS,VLOOKUP(A1,NewTable,2,0),0)

I used the number 0 instead of FALSE above. They mean exactly the same thing.

Aladin

This code is attached to a form button. It gives a user box that asks for a search code. It will take wild cards so you can look for any thing, like a string containing "CO" in any position or any combination of codes.

You will need to change some of the addressing and the length of the parameters to fit your needs. JSW

Sub Search_Complex_Code()
Dim Temp As String
Dim Tsearch As String
Dim Numsub
Dim Mcan
Temp = Application.InputBox(Prompt:="Please enter the Complexity Code to search for;" & Chr(13) & "Use a [ 7 digit search code ], only!" & Chr(13) & "You may use [ ? ] as a place holders, for digits which are not important!" & Chr(13) & "Example: ?G?????, [6 codes, 7 digits, ODA = 2 digits!]", Title:="Enter the Complexity Code to Lookup!", Type:=2)

If Temp = "False" Then
Numsub = 2

ElseIf Temp = "" Then
Numsub = 3

Else
Numsub = 1
End If

Select Case Numsub

Case 1
Tsearch = "" = " & Temp & """
With Worksheets("Load")
.AutoFilterMode = False
.Rows(5).AutoFilter
.Rows(5).AutoFilter Field:=6, Criteria1:=Temp, VisibleDropDown:=False
.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Search").Cells(1, 1)
Application.CutCopyMode = False
.AutoFilterMode = False
End With
Sheets("Search").Select
Range("A1").Select

Case 2
Sheets("Search").Select
Range("A1:A6").Select
Selection.EntireRow.Delete
Range("A1").Select
Sheets("Load").Select
Range("A1").Select


Case 3
Mcan = MsgBox("Search Canceled, search criteria is Blank?", vbOKOnly, "BLANK SEARCH!")
Sheets("Search").Select
Range("A1:A6").Select
Selection.EntireRow.Delete
Range("A1").Select
Sheets("Load").Select
Range("A1").Select

End Select
End Sub