Vlookup for a certain part and print the min

soumen21

New Member
Joined
Aug 16, 2019
Messages
29
Hi, I have a table like this in sheet1

Part NumberYears
AAA1
AAA1
AAA2
BBB4
CCC3
BBB4
AAA3
BBB3
CCC6


In sheet 2, I want to lookup for a particular Part Number and get the min of the value.
Something like this

Part NumberYears
AAA1
BBB3
CCC3


Could you please help.

Regards
Soumen
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Book5
ABC
1Part NumberYears
2AAA1
3AAA1
4AAA2
5BBB4
6CCC3
7BBB4
8AAA3
9BBB3
10CCC6
11
12
13Part NumberYearsColumn1
14AAA11
15BBB33
16CCC33
Sheet1
Cell Formulas
RangeFormula
B14:B16B14=MINIFS(Table1[Years],Table1[Part Number],[@[Part Number]])
C14:C16C14=MIN(FILTER(Table1[Years],Table1[Part Number]=[@[Part Number]]))
 
Upvote 0
Try
Excel Formula:
=AGGREGATE(15,6,(B2:B10)/(A2:A10=C2),1)
Where C2 contains the lookup value
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Book5
ABC
1Part NumberYears
2AAA1
3AAA1
4AAA2
5BBB4
6CCC3
7BBB4
8AAA3
9BBB3
10CCC6
11
12
13Part NumberYearsColumn1
14AAA11
15BBB33
16CCC33
Sheet1
Cell Formulas
RangeFormula
B14:B16B14=MINIFS(Table1[Years],Table1[Part Number],[@[Part Number]])
C14:C16C14=MIN(FILTER(Table1[Years],Table1[Part Number]=[@[Part Number]]))
Thnaks a lot
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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