Adding Additional Criteria to a formula

It would be helpful if you had said what the answer was supposed to be for your CETR problem. I assumed it was supposed to be the same as your original formula except when Table1 had a 1 for either the length or weight--and both length and weight constraints are satisfied.

If B4 is 1, C4 is 19,900, then the following formula in cell E4 returns CETR when copied down to row 15.
=IF(OR($B$4="",$C$4=""),"",
IF(AND($B$4>=INDEX(Table1[Linear Feet],ROWS(E$3:E3)),$C$4>=INDEX(Table1[Weight],ROWS(E$3:E3)),OR(INDEX(Table1[Linear Feet],ROWS(E$3:E3))=1,INDEX(Table1[Weight],ROWS(E$3:E3))=1)), INDEX(Table1[SCAC],ROWS(E$3:E3)),
IFERROR(INDEX(Table1[SCAC],SMALL(IF((Table1[Linear Feet]>=$B$4)*(Table1[Weight]>=$CS$4),ROW(Table1[SCAC])-MIN(ROW(Table1[SCAC]))+1),ROWS(E$3:E3))),"")))

I have a VBA approach. Which will be much faster than Array Formula:

Sub UpdateSCAC()
Dim DataSh As Worksheet, ResultSh As Worksheet
Dim SearchRow As Integer, CopyToRow As Integer
Dim DataLR As Long, ResultLR As Long

Set DataSh = Sheets(1)
Set ResultSh = Sheets(2)
DataLR = DataSh.Range("A" & Rows.Count).End(xlUp).Row
ResultLR = ResultSh.Range("E" & Rows.Count).End(xlUp).Row
If ResultSh.Range("B4").Value = "" Then
    MsgBox "Please Enter Input Linear Feet"
    Exit Sub
End If
If ResultSh.Range("C4").Value = "" Then
    MsgBox "Please Enter Input Weight"
    Exit Sub
End If
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With
ResultSh.Range("E4:E" & ResultLR).ClearContents
CopyToRow = 4
For SearchRow = 2 To 1000
    If DataSh.Range("B" & SearchRow).Value >= ResultSh.Range("B4").Value And DataSh.Range("C" & SearchRow).Value >= ResultSh.Range("C4").Value Or DataSh.Range("C" & SearchRow).Value = 1 Or DataSh.Range("B" & SearchRow).Value = 1 Then
        DataSh.Range("A" & SearchRow).Copy
        ResultSh.Range("E" & CopyToRow).PasteSpecial xlPasteValues
        CopyToRow = CopyToRow + 1
        Application.CutCopyMode = False
    End If
Next SearchRow
ResultSh.Activate
ResultSh.Range("E2").Select

With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Select allOpen in new window

Please find attached I have included a Shape which you can click once you fill B4 & C4. If these cells are blank, it will exit Sub.
Hope this helps.
PS: I have added VBA as well in your topic.
Carrier-Volume-Rate-Estimate_v2.xlsm

Have I approached this incorrectly, when I am putting in an a Linear FT and Weight that is lower than what the SCAC is willing to do such as ARFW that has LF 10 and W 4000. Example: When I put in LF 9 and W 3500 it still shows the ARFW when it should not.

Josh,

Are we on the same page? Your formula condition was if LF >= than Input & W is >= Input, it must pull all SCAC, addition to that you wanted if LF = 1 or W = 1. VBA will pull all these SCAC matching four criteria.

ARFW LF is 10 & Weight is 4000. If you are trying to put 9 or 10 which is obviously matching LF in Carrier Rules sheet.

Please check again

if we input a LF or W in the input field that is lower than the LF or W in the table it should not pull over that SCAC. The table has inputs of the carriers who will offer a quote for their lowest LF and W.

So if we input something that is lower than they are will to offer either in the LF or the W their SCAC should not show up. In addition to that the carriers with a LF or W with a 1 means they do not have a lowest LF or W.

I hope this helps explain the goal I am trying to accomplish. My apologies for any confusion.

I think if I switch the > to and < it will work the way I want it to but it is now showing everything that has blank as well... how do I make it skip past the blanks?

  If Cells(A, 1) <> "" Then if this works where is the best place to put in the VBA @Shums?

Josh, please give me one example which is not working. I will check here with VBA.

LF 8 and W 3500   ARFW which has LF 10 W 4000 should not pop up but it seems to come up.

I think there may be confusion, partly in my explanation.

If the input number in LF or W is greater than or equal to a number in Table 1 pull over number that are less than or equal to the input number. If the number in Table1 is greater than the number that has been input do not bring it over. The carrier has given us the lowest LF or W they are willing to Quote for the a volume. The 1 in the Table1 in either the LF or W indicates they do not have a minimum LF or W so they would be willing to Quote a volume for us.  I also need it to skip past any of the SCACs that have blank LF and W so they don't return.

I hope this helps

but your formula says otherwise: IF((Table1[Linear Feet]>=$B$4)*(Table1[Weight]>=$CS$4)
Great than or equal to Input.

That was my mistake after testing with my team I had it wrong. My apologies.

OK no worries. Let me change

What about those which has no values?

Please find attached excluding 0's as well. Let me know what further changes needed.
Carrier-Volume-Rate-Estimate_v3.xlsm

This is getting closer, however it is not showing for the input LF 8 W 3500 it is not showing a match for the carriers that have LF of 8 such as ESTE,OVLD, RDWY, UPGF, WTVA.  The goal is to return if the either the LF or W matches the criteria. If one matches but the other does not that is ok still pull it over as long as LF or W matches one of the input values.

Shums, You have been a very big help on this project. I can't thank you enough

You're Welcome Josh! Glad I was able to help you gain.