Return last value - matching text in two columns

[smide]

Hello.


In columns C and D (C3:D600) I have a product list (about 25 different products) and in columns F and G (F3:G600) their corresponding prices.
When product is in column C his price is in column F, and when product is in column D his price is in column G.


In column I there is a list of ALL products.


I need last value for each product - results should be in column J. (J3...)


example.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Products[/TD]
[TD="align: center"]Results[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]apple[/TD]
[TD]orange[/TD]
[TD][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD]apple[/TD]
[TD="align: center"]29[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]cookie[/TD]
[TD]flour[/TD]
[TD][/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD]orange[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]toys[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]2[/TD]
[TD][/TD]
[TD]cookie[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]orange[/TD]
[TD]flour[/TD]
[TD][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]31[/TD]
[TD][/TD]
[TD]flour[/TD]
[TD="align: center"]31[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]cookie[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]29[/TD]
[TD][/TD]
[TD]toys[/TD]
[TD="align: center"]27[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]orange[/TD]
[TD]toys[/TD]
[TD][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]27[/TD]
[TD][/TD]
[TD]....[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]...[/TD]
[TD]....[/TD]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

*Note: there is no two identical products in the same row

 

[Domenic]

Try...

J3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=LOOKUP(9.99999999999999E+307,IF($C$3:$C$8=I3,$F$3:$F$8,IF($D$3:$D$8=I3,$G$3:$G$8)))

Adjust the ranges accordingly.

Hope this helps!

 

[JoeMo]

Here's a macro alternative. Assumes the layout of your data is as in post #1 and generates the results as in that layout.

Sub LastPrice()
Dim R As Range, Vin As Variant, Vprods As Variant, d As Object, x, i As Long, j As Long, k As Long
Set R = Range("C3:G" & Cells(Rows.Count, "G").End(xlUp).Row)
Vin = R.Value
Application.ScreenUpdating = False
Range("I2:J2").Value = Array("Products", "Results")
Set d = CreateObject("Scripting.dictionary")
For i = LBound(Vin, 1) To UBound(Vin, 1)
    For j = LBound(Vin, 2) To UBound(Vin, 2) - 3
        x = d.Item(Vin(i, j))
    Next j
Next i
With Range("I3").Resize(d.Count, 1)
    .Value = Application.Transpose(d.keys)
    Vprods = .Resize(, 2).Value
    For i = LBound(Vprods, 1) To UBound(Vprods, 1)
        For j = LBound(Vin, 1) To UBound(Vin, 1)
            For k = LBound(Vin, 2) To UBound(Vin, 2)
                If Vin(j, k) = Vprods(i, 1) Then
                    Select Case k
                        Case 1: Vprods(i, 2) = Vin(j, 4)
                        Case 2: Vprods(i, 2) = Vin(j, 5)
                    End Select
                End If
            Next k
        Next j
    Next i
    .Resize(, 2).Value = Vprods
End With
Application.ScreenUpdating = True
End Sub