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.

	C	D	E	F	G	H	I	J
1
2							Products	Results
3	apple	orange		12	5		apple	29
4	cookie	flour		24	7		orange	8
5	toys	apple		18	2		cookie	6
6	orange	flour		14	31		flour	31
7	cookie	apple		6	29		toys	27
8	orange	toys		8	27		....
9	...	....		...	...

<tbody>
</tbody>

*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