Finding Latest Value in List of Many Items

[karse]

Hi! I'm trying to find a way to do a vlookup/use a pivot table to find the last value in column B for each part number in column A (ie. 48200-091-02 would give me 0, LV429235 would be 0, etc). It's not always the max/min, so a pivot table isn't working.

48846	25
LV429235	0
48200-091-02	1000
48200-091-02	0
BBV59050-F	400
LV429235	100
LV429235	200
LV429235	0
LV429236	50

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

Here are 2 option to consider, depending on how dynamic you need the results to be. In each case the '1000' values in my formulas need to be something at least as large as the last row of your data.

Option A (not dynamic)
1. In an empty column (I've used column D) put the formula shown into row 2 in that column, leaving the cell above blank.
2. Select the data (A1:B10 in my case) & on the Data ribbon tab choose 'Advanced' in the Sort & Filter section -> Copy to another location -> List range should already be populated with $A$1:$B$10 -> Criteria range: $D$1:$D$2 -> Copy to: F1 -> OK

Book1
	A	B	C	D	E	F	G
1	Part No	Value				Part No	Value
2	48846	25		TRUE		48846	25
3	LV429235	0				48200-091-02	0
4	48200-091-02	1000				BBV59050-F	400
5	48200-091-02	0				LV429235	0
6	BBV59050-F	400				LV429236	50
7	LV429235	100
8	LV429235	200
9	LV429235	0
10	LV429236	50
11
Option A

Cell Formulas
Range	Formula
D2	=COUNTIF(A2:A$1000,A2)=1

Option B (dynamic, but more formula overheads)
Each of these formulas copied down as far as you might need

Book1
	A	B	C	D	E
1	Part No	Value		Part No	Value
2	48846	25		48846	25
3	LV429235	0		LV429235	0
4	48200-091-02	1000		48200-091-02	0
5	48200-091-02	0		BBV59050-F	400
6	BBV59050-F	400		LV429236	50
7	LV429235	100
8	LV429235	200
9	LV429235	0
10	LV429236	50
11
Option B

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX($A$2:$A$1000,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$1000)+($A$2:$A$1000=""),),0)),"")
E2	=IF(D2="","",LOOKUP(9.99E+307,B$2:B$1000/(A$2:A$1000=D2)))

 

[Aladin Akyurek]

Like this:

=LOOKUP(9.99999999999999E+307,1/($A$2:$A$10=E2),$B$2:$B$10)

where E2 houses a value of interest like 48200-091-02.