Return the second to last value - data in two columns

smide

Board Regular
Joined
Dec 20, 2015
Messages
162
Office Version
  1. 2016
Platform
  1. Windows
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.

ABCDEFG
1appleorange72apple819
2grapescabbage1113orange12
3tomatoapple59.........
4orangeplum16
5applecarrot194
6cabbageapple228

<tbody>
</tbody>


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).
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
ABCDEFG
1appleorange72apple819
2grapescabbage1113orange12
3tomatoapple59
4orangeplum16
5applecarrot194
6cabbageapple228
7
Second Last
 
Upvote 0
Thanks for reply Peter.
Sorry, I forgot to mention, I'm using excel 2007 so 'aggregate' function is not an option for me...:confused:
Any idea what can I use instead of aggregate function?
 
Upvote 0
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
ABCDEFG
1appleorange72apple819
2grapescabbage1113orange12
3tomatoapple59
4orangeplum16
5applecarrot194
6cabbageapple228
7
Second Last (2)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top