CODEWITHSUNDEEP
vbaexcel
Vikky
Vikky

Reputation: 764

Get Price from Table in excel using lookup or macro

I have table "PriceList" in excel with below structure

Type       Thickness     width    Height     Price
iron          5            7        10         20
iron          10           10       15         24
iron          12           14       17         26

how can i find the price if some-one enters type iron, thickness 10, width 9, height 14 using vlookup or macro. i have tried using vlookup but its not works and later on i found on google that Vlookup only retrieve data with one parameter. Can i do this using macro like we do for selecting sheet data using queries

select * from [sheet1$] where col1=x

Please suggest??

Edit: i have calculated the above using this. Now i am calling this macro on sheet change event like below

    Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 41 Or Target.Column = 43 Or Target.Column = 45 Or Target.Column = 46 Then
Dim i As Currency
i = Calculate_CorePrice(cell(Target.Row, 46).Value, cell(Target.Row, 45).Value, cell(Target.Row, 41).Value, cell(Target.Row, 43).Value, "RWJ Doorset Schedule", "ED15")
cell(Target.Row, 134).Value = i
End If

End Sub

if i debug the function itself with hard-coded values, it is working but if i call the function as in above way, it does not return value and i am not able to debug sheet_change event.

How can i debug this event.

Upvotes: 1

Views: 1380

Answers (3)

RGA
RGA

Reputation: 2607

The best would be to loop through the data, using an if statement to check the data tables. (This could be placed in a sub or in a function depending on how you want the data to be retrieved)

Function GetPrice(Type As String, Thickness As Integer, Width As Integer, Height As Integer)

Dim ItemRow As Range, SearchColumn As Range
'Dim Price As Double   'Use variable if this were a sub
Set SearchColumn = Range(Cells(2,1),Cells(ActiveSheet.UsedRange.rows.count,1))
For Each ItemRow In SearchColumn
    If ItemRow.Value = Type AND ItemRow.Offset(0,1).Value >= Thickness AND ItemRow.Offset(0,2).Value >= Width AND ItemRow.Offset(0,3).Value >= Height AND ItemRow.Offset(-1,1).Value < Thickness AND ItemRow.Offset(-1,2).Value < Width AND ItemRow.Offset(-1,3).Value < Height Then
         'Price = ItemRow.Offset(0,4).Value
         GetPrice = ItemRow.Offset(0,4).Value
         Exit For
    End If
Next ItemRow

End Function

Edit: Given your comment on another answer, I've adjusted the code to find the value as i believe you are looking for

Upvotes: 1

Ralph
Ralph

Reputation: 9444

It seems I am just a bit late with my answer and @BruceWayne beat me to it. Still, I will add my solution as well as there is a nice little illustration to it:

enter image description here

Basically, it is the same proposal has been already proposed. Just use an array formula and the & sign to combine all criteria into one. In the end a criteria will look like this iron101015 (for example). And it will be compared to all the combinations in the table.

Upvotes: 2

BruceWayne
BruceWayne

Reputation: 23283

Assuming your columns are A, B, C, etc. You can use an Index/Match formula as an array (enter with CTRL+SHIFT+ENTER).

If the user enters the type, thickness, width, and height in W1, X1, Y1, and Z1, you can use:

=Index($E$2:$E$10,Match(W1&X1&Y1&Z1,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10&$D$2:$D$10,0))

It's convoluted, but basically the Match part is you are looking for W1 in the range A2:A10, then whatever is in X1, you will search B2:B10, etc.

Upvotes: 3

Related Questions