Excel VBA- VLOOKUP with multiple criteria-CodePudding

I need a VBA code that searches for a specific Name (first dropdown), Products (second drop-down), then returns the unit price. I can use VLOOKUP to search names and return the unit price but I need to search for names and products and be able to pull the prices quickly. I used Evaluate function but the result is #VALUE!

Sub unitPrice()

Set sh4 = ThisWorkbook.Sheets("Invoice")
Set sh5 = ThisWorkbook.Sheets("Unit Price")

sh4.Range("H18") = _
sh4.Evaluate("MATCH(" & sh4.Cells(11, 1).Address(False, False) _
& "&" & sh4.Cells(18, 1).Address(False, False) _
& ",'Sh5!B2:B5&sh5!A2:A5,0)")

End Sub

Screenshot of Invoice and Unit Price sheet

CodePudding user response：

I am assuming that you have two tables (insert > table): tblInvoice and tblUnitPrice. It is much easier to reference them in VBA via listobject than without. If you are not using tables you have to adjust the ranges accordingly.

What my code does: It inserts an INDEX/MATCH-Formula to retrieve the Unitprice for all rows in the table - and then writes the pure values back to the cells.

Public Sub updateUnitPricesInInvoice()

Dim loInvoice As ListObject
Set loInvoice = ThisWorkbook.Worksheets("Invoice").ListObjects("tblInvoice")

With loInvoice.ListColumns("UnitPrice").DataBodyRange
    .Formula2 = "=INDEX(tblUnitPrices[UnitPrice],MATCH(1,(tblUnitPrices[Name]=[@Name])*(tblUnitPrices[Product]=[@Product])))"
    .value = .value
End With

End Sub

CodePudding user response：

This is the solution without tables/listobjects:

Assumption: you have added names for the following cells on invoice sheet

A11: customer
A17: labelDescription
H17: labelUnitPrice
H28: labelTotalAmount

In the first step we retrieve the range between the two labels "UnitPrice" and "TotalAmount" - that's where the formula goes.

Then the formula is written to that range - using again INDEX/MATCH. In case there is not description nothing is displayed (there ISERROR)

And again: after calculation formulas are replaced by their values

Option Explicit

Public Sub updateUnitPricesInInvoice()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Invoice")

Dim rgUnitPrices As Range
Set rgUnitPrices = getRangeBetweenTwoLabels(ws, "labelUnitPrice", "labelTotalAmount")


With rgUnitPrices
    .Formula2 = "=IFERROR(INDEX(UnitPrice!C:C,MATCH(1,(UnitPrice!A:A=Invoice!" & ws.Range("labelDescription").Offset(1).Address(False, True) & ")*(UnitPrice!B:B=customer),0)),"""")"
    .Value = .Value
End With

End Sub


Private Function getRangeBetweenTwoLabels(ws As Worksheet, _
    label1 As String, label2 As String)
    
Dim cStart As Range: Set cStart = ws.Range(label1).Offset(1)
Dim cEnd As Range: Set cEnd = ws.Range(label2).Offset(-1)

Set getRangeBetweenTwoLabels = ws.Range(cStart, cEnd)

End Function