Look through 4 columns and return value in 5th

[maykinit]

I could REALLY use some help from the experts here on this one...
I would like to accomplish the following.
I have four columns of data...

A B C D E
10 100 A 100RF 111A111-01
20 200 B 600FF 222A222-02
etc...

There could be potentially hundreds of rows.
Using search criteria from variables I need to look for the right combination of values found in A, B, C and D and return me the value found in E. I've been hacking away at this and could REALLY use some expert advice, preferably examples of how to make this happen. I believe I'm explaining this well enough understanding that there is often much misunderstanding coming from misinterpretation. Thanks so very much for any help you might choose to offer....

 

[Andrew Poulsom]

Try eg:

=INDEX(E1:E100,MATCH(1,INDEX(((A1:A100=F1)*(B1:B100=F2)*(C1:C100=F3)*(D1:D100=F4)),),FALSE))

where your criteria are in F1:F4.

 

[maykinit]

Thank you very much for the response but I really need this to work with a VBA routine.

 

[Andrew Poulsom]

Do you have an existing VBA routine that you want to amend?

 

[maykinit]

No sir, the attempts I've made have been futile...

 

[Andrew Poulsom]

So where would you search criteria be?

 

[maykinit]

I am parsing a part number. I'd like to load variables with those values and then use those values as the criteria for the search through the columns. Does that make sense? I'm open to any and all suggestions but that's what I have in mind.

 

[Andrew Poulsom]

Here's an example:

Sub Test()
    Dim Var1, Var2, Var3, Var4
    Dim Result
    Dim Cell As Range
    Var1 = InputBox("Variable 1?")
    Var2 = InputBox("Variable 2?")
    Var3 = InputBox("Variable 3?")
    Var4 = InputBox("Variable 4?")
    Result = ""
    With Worksheets("Sheet1")
        For Each Cell In .Range("A1:A100")
            If Cell.Value = Val(Var1) And Cell.Offset(, 1).Value = Val(Var2) And Cell.Offset(, 2).Value = Var3 And Cell.Offset(, 3).Value = Var4 Then
                Result = Cell.Offset(, 4).Value
                Exit For
            End If
        Next Cell
    End With
    If Len(Result) Then
        MsgBox Result
    Else
        MsgBox "No match found."
    End If
End Sub

 

[maykinit]

Awesome... that's exactly what I was after! Thank you so very much for the help Andrew! Your the best!

 

[maykinit]

I wonder if you could advise how you would embellish this so that if the criteria was the same on more than one row it would return the value from the fifth column for each row that matches the criteria?