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 :)

As you mention VBA, do you have any attempt(s) with it to show us? Without VBA this won't be feasible, I believe. With VBA however, it becomes a lot easier since you can use variables to store the row and column from the Match function (for both of them) and then do a simple check of that cell's background color. — Notus_Panda, Mar 03 '23 at 09:04
@Notus_Panda unfortunately I don't know how where to start to solve it with 3 conditions ... — Tom, Mar 03 '23 at 09:07
Alright, here are a few links to help you further (we are not a site to provide full code, we're here to assist): using match in VBA (you can use it on a column (for the partner name) or a row (for the type of apple)): https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.match using variables: https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-variables and background color: https://stackoverflow.com/questions/520570/return-background-color-of-selected-cell — Notus_Panda, Mar 03 '23 at 09:16
If the cells are conditionally formatted, you could probably do it without vba since you could implement the logic of the condition into the formula retrieving the information. — Notus_Panda, Mar 03 '23 at 09:22
@Notus_Panda the cells are not conditionally formatted, but I have something to start with. Thank you for your tips and links :) — Tom, Mar 03 '23 at 09:47

Foxfire And Burns And Burns · Accepted Answer · 2023-03-03T09:55:15.357

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.

VBA Excel Lookup on multiple conditions

1 Answers1