LOOKUP 2nd to last and 3rd to last

[skovatov]

Hello, I could use a little help please in determining the correct formula to use. See uploaded Sample Chart. I am trying to lookup the last price, the second to last price, and the third to last price for a widget in a list of widgets, and the list of widgets and prices will continue to be added to. If I add another Widget B, and enter the advertised price, I'd like the next three columns to auto populate with the last price for Widget B, the second to last price for Widget B, and the third to last price for Widget B. I hope I'm doing an okay job explaining the desired outcome in conjunction with the supplied sample chart. Thank you, in advance for your time and feedback, it's greatly appreciated.

thank you!

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1 (version 1).xlsb
	A	B	C	D	E
1	Mfr Part Number	Advertised Price	Last Advertised Price	2nd to Last Advertised Price	3rd to Last Advertised Price
2	Widget A	$ 2.00	N/A	N/A	N/A
3	Widget B	$ 3.00	N/A	N/A	N/A
4	Widget C	$ 4.00	N/A	N/A	N/A
5	Widget D	$ 1.50	N/A	N/A	N/A
6	Widget D	$ 1.00	$ 1.50	N/A	N/A
7	Widget B	$ 2.00	$ 3.00	N/A	N/A
8	Widget A	$ 6.00	$ 2.00	N/A	N/A
9	Widget C	$ 3.00	$ 4.00	N/A	N/A
10	Widget A	$ 2.50	$ 6.00	$ 2.00	N/A
11	Widget B	$ 4.00	$ 2.00	$ 3.00	N/A
12	Widget C	$ 2.10	$ 3.00	$ 4.00	N/A
13	Widget D	$ 6.00	$ 1.00	$ 1.50	N/A
14	Widget B	$ 7.00	$ 4.00	$ 2.00	$ 3.00
15	Widget C	$ 1.00	$ 2.10	$ 3.00	$ 4.00
16	Widget D	$ 5.00	$ 6.00	$ 1.00	$ 1.50
17	Widget A	$ 3.00	$ 2.50	$ 6.00	$ 2.00
Sheet4

Cell Formulas
Range	Formula
C2:E17	C2	=IFERROR(INDEX($B:$B,AGGREGATE(14,6,ROW($A$1:$A1)/($A$1:$A1=$A2),COLUMNS($C2:C2))),"N/A")

 

[jtakw]

Hi,

Here's another way, formula copied down and across:

Book3.xlsx
	A	B	C	D	E
1
2	A	2	N/A	N/A	N/A
3	B	3	N/A	N/A	N/A
4	C	4	N/A	N/A	N/A
5	D	1.5	N/A	N/A	N/A
6	D	1	1.5	N/A	N/A
7	B	2	3	N/A	N/A
8	A	6	2	N/A	N/A
9	C	3	4	N/A	N/A
10	A	2.5	6	2	N/A
11	B	4	2	3	N/A
12	C	2.1	3	4	N/A
13	D	6	1	1.5	N/A
14	B	7	4	2	3
15	C	1	2.1	3	4
16	D	5	6	1	1.5
17	A	3	2.5	6	2
Sheet846

Cell Formulas
Range	Formula
C2:E17	C2	=IFERROR(LOOKUP(2,1/($A$1:$A1=$A2),B$1:B1),"N/A")