Compare a value in a cell to a row/column on another sheet.

[shawnashannon]

I hope someone is smarter than I with VBA and can help me out with this one.
Basically I am looking for Excel VBA code to compare 4 cell values from 1 sheet to 4 columns on another sheet, but the 4 values on other sheet must be in same rows. I am but a VBA beginner and I have been trying for days with no luck.
I want it to go something like this:

IF A5 (on sheet1) = 40 AND B5 < (A value in column M on sheet2) AND C5 < (a value in column E on sheet 2 but in the same row as the previous) AND D5 < (a value in column F on sheet 2, also in same row as previous 2)
THEN display model from column C on sheet 2 also from same column into sheet 1 G5

for example (see attachment) on sheet1 if you enter 40 into A5, 3846 into B5, 1492 into C5 and 1999 into D5 Then "MODEL19" (from sheet2) should show up in G5.
I had put in a 'button' but it is not needed if there is a better/easier way to do this.

Can anyone help me with this, please?

Thank you kindly in advance

[p24leclerc]

Looking at your workbook and for your example, there are 48 models that answer to all your criteria. Do you want the first one only? Or there is someting I did not understand. What does the 40 in cell A5? I don't see how you use it.

[AlphaFrog]

This formula in G5 will return the first that matches all criteria.

This is an array formula.
Confirm with Ctrl+Shift+Enter

=IF(AND(A5=40,COUNT(B5:D5)=3), INDEX(Sheet2!C8:C75,MATCH(1,(B5<Sheet2!M8:M75)*(C5<Sheet2!E8:E75)*(D5<Sheet2!F8:F75),0)),"")

[shawnashannon]

AlphaFrog, This formula does not seem to work.
p24leclerc, Yes, I would like the first only.
The 40 implies using columns M,N and O. As opposed to if one were to enter say 60 then one would need to look in columns S,T and U... But I will deal with that later. "40" is the most common one I would need

Thank you.

[shawnashannon]

Bump, not yet answered.

[shawnashannon]

Bump, not solved.

[p24leclerc]

Sorry for the delay.
Here is a macro that could help you. I also attached a workbook with the macro in it.
I commented the macro so you could see what the code stands for.

Sub Button5_Click()
Dim C_ell As Range
Dim sh As Worksheet
Application.ScreenUpdating = False
Worksheets ("Sheet2")
'
'Select any cell in the table for the AutoFilter to determine the proper range to filter
Range("C10").Select
'Check if FILTER mode already on
If Not ActiveSheet.AutoFilterMode Then
   'If not, then activate AutoFilter
   Selection.AutoFilter
Else
   'If already activated, then ShowAllData (Kind of reset)
   ActiveSheet.ShowAllData
End If
'
'Create 3 filters
'They self align to the proper columns based on value in cells A5 in Sheet1
ActiveSheet.Range("$C$8:$AD$" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=-1 + (Sheets("Sheet1").Range("A5") / 10 * 3), Criteria1:=">" & Sheets("Sheet1").Range("B5"), _
    Operator:=xlAnd
ActiveSheet.Range("$C$8:$AD$" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=(Sheets("Sheet1").Range("A5") / 10 * 3), Criteria1:=">" & Sheets("Sheet1").Range("C5"), _
    Operator:=xlAnd
ActiveSheet.Range("$C$8:$AD$" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=1 + (Sheets("Sheet1").Range("A5") / 10 * 3), Criteria1:=">" & Sheets("Sheet1").Range("D5"), _
    Operator:=xlAnd
'
'Copy first filtered value in Sheet1 cell G5
Sheets("Sheet1").Range("G5") = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1)
'Turn Autofilter off
Selection.AutoFilter
'Get back to Sheet1 to see the result
Sheets("Sheet1").Select
Range("G5").Select
'
Application.ScreenUpdating = True

End Sub

Hope this helps