Return the second to last value - data in two columns

[smide]

Hello.


In columns A and B (A1:A600 and B1:B600) I have product names and in columns C and D their prices. When product is in column A his price is in column C (same row) and when product is in column B his price is in column D.


In column E I have a list of all products (E1:E50).


With array formula
=LOOKUP(9.99999999999999E+307,IF($A$1:$A$600=E1,$C$1:$C$600,IF($B$1:$B$600=E1,$D$1:$ED$600))) I find the last price for each product.


Is there any way I could adapt somehow this formula to find the second last price for each product?


Result(s) for last price are in column F and for second last price in column G.


example.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/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]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]grapes[/TD]
[TD="align: center"]cabbage[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]tomato[/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"]plum[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"]carrot[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]cabbage[/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


explanation: Last value for apple is in cell D6, so result is cell F1 is 8. Second last value for apple is in cell C5, result in G1 is 19. Same story for orange (and all other products).

 

[Peter_SSs]

As with a previous similar thread of yours that I participated in, I am assuming that any item will not appear in both columns A & B on the same row.
If that is so then you could try this, copied across and down for last, 2nd last, 3rd last etc

Excel Workbook
	A	B	C	D	E	F	G
1	apple	orange	7	2	apple	8	19
2	grapes	cabbage	11	13	orange	1	2
3	tomato	apple	5	9
4	orange	plum	1	6
5	apple	carrot	19	4
6	cabbage	apple	22	8
7
Second Last

 

[smide]

Thanks for reply Peter.
Sorry, I forgot to mention, I'm using excel 2007 so 'aggregate' function is not an option for me...
Any idea what can I use instead of aggregate function?

 

[Peter_SSs]

Sorry, I forgot to mention, I'm using excel 2007

Since that is now 12 years old, you should mention that first-up in any question you ask.

Excel Workbook
	A	B	C	D	E	F	G
1	apple	orange	7	2	apple	8	19
2	grapes	cabbage	11	13	orange	1	2
3	tomato	apple	5	9
4	orange	plum	1	6
5	apple	carrot	19	4
6	cabbage	apple	22	8
7
Second Last (2)