Code by Claus that looked up four items and returned a fifth item.

I need it to lookup three and return the fourth.

First item is in column B6:B500+-
Second is in column C same range
Third is in column D same range

and the return item is in column H same range as others.

Items 1 thru 4 will be on the same row.

I have made some change to the original code but to dense to get it to look for three return fourth.

The message box is good and M9, 10, 11 etc. are fine for a return targets at present.

Thanks.
Howard


Option Explicit
Option Compare Text

Sub Lookup_Four_Return_Fifth2_Claus()
'// Lookup three return fourth
Dim lngLstRow As Long
Dim str1 As String
Dim str2 As String
Dim i As Long

Dim intVStore() As Double

Dim intValVar As Integer
Dim wsh As Worksheet

str1 = InputBox("Input Material:", "Material") & InputBox("Input Pipe Non. Diameter:", "Pipe Nom Dia") _
& InputBox("Input Pipe Press Class:", "Pipe Press Cls")

For Each wsh In ThisWorkbook.Worksheets

With wsh
lngLstRow = .UsedRange.Rows.Count

For i = 2 To lngLstRow

str2 = .Cells(i, 1) & .Cells(i, 2) & _
.Cells(i, 3)

If StrComp(str1, str2, 1) = 0 Then

ReDim Preserve intVStore(intValVar)
intVStore(intValVar) = .Cells(i, 7).Value

Range("K1") = .Cells(i, 2) & " " & .Cells(i, 3) & " " & _
.Cells(i, 4)
Range("K2") = intVStore() 'Price

intValVar = intValVar + 1
End If
Next

End With

Next wsh

If intValVar = 0 Then
MsgBox "No items found"
Exit Sub
Else
'MsgBox "The Price is: " & WorksheetFunction.Max(intVStore())
End If
End Sub