VBA Excel Lookup on multiple conditions

Question

I have two workbooks where I need to do a lookup in between and obtain a value. Workbook A with sheet "prices" needs to do a lookup for a specific price in Workbook B sheet "list" on multiple conditions in VBA Excel. I'm not sure how to solve it. With an Index/Match, a XLookup or maybe another solution.

Workbook A sheet "prices" cell B5 needs to obtain the price from Workbook B sheet "list" cell D3 on the following conditions: a. the name "4Fruits" b. name of the fruit "Green Apple" c. the price must be marked yellow.

Hope someone can solve this, thanks :)

Answer 1

You can do this easily with MATCH inside an UDF. My example is just in the same workbook but it will work on different workbooks.

Formula in cell B4 is:

=GET_PRICE($D$1:$G$4,$A$1,A4)

Code of this UDF:

Public Function GET_PRICE(ByVal RngPrices As Range, ByVal vPartner As String, vType As String) As Variant

Dim vRow As Long
Dim vColumn As Long

With Application.WorksheetFunction
    vRow = .Match(vPartner, RngPrices.Columns(1), 0)
    vColumn = .Match(vType, RngPrices.Rows(1), 0)
End With

'check color
If RngPrices.Cells(vRow, vColumn).Interior.Color = vbYellow Then
    GET_PRICE = RngPrices.Cells(vRow, vColumn).Value
Else
    'not yellow, return N/A
    GET_PRICE = "Nope"
End If

End Function

Notice this UDF will work only if the cells interior color is yellow (not Condittional formatting) and the partners name and products name are exactly the same.

Now, let's erase yello color of Green apple and see how the function will not bring the price because the cell it's not yellow.