Try this
Option Explicit
'Create an availability function that accepts the following arguments
' Material code (cell A) - the material code to be analyzed
' Location - a Warehouse Location String (cells H, I). It can be “Porto” or "Lisbon"
' Quantity - an integer with the quantity to order.
'The function should return one of three possible results:
' “Available for immediate delivery” if the warehouse is located have the quantity in stock.
' “Available for delivery” if the quantity ordered is available on the
' total stock of the two warehouses (i.e. quantity ordered <Stock Port0 + Stock Lisboa)
' “Unavailable” if the quantity ordered is greater than the sum of the stock in warehouses
Function Availablity(Mtl As String, Loc As String, Qty As Long) As String
Dim rowMtl As Long, colLoc As Long, Qty1 As Long, Qty2 As Long
With Application.Caller.Parent
rowMtl = 0
On Error Resume Next
rowMtl = Application.WorksheetFunction.Match(Mtl, .Columns(1), 0)
On Error GoTo 0
If rowMtl = 0 Then
Availablity = ""
Exit Function
End If
Qty1 = .Cells(rowMtl, 8).Value ' Porto
Qty2 = .Cells(rowMtl, 9).Value ' Lisboa
End With
If Loc = "Porto" And Qty <= Qty1 Then
Availablity = "Available for immediate delivery"
ElseIf Loc = "Lisboa" And Qty <= Qty2 Then
Availablity = "Available for immediate delivery"
ElseIf Qty <= (Qty1 + Qty2) Then
Availablity = "Available for delivery"
Else
Availablity = "Unavailable"
End If
End Function