SOLVED

New Contributor

# Finding the low and high price of a part #

Hello - I am looking for some help with a spreadsheet I am working on. I have a sheet with a tab that has part#'s that have multiple purchases. There are a lot of the same part#'s on them.

I have created another tab and would like to find the highest and lower part# within the first tab for the respective part#.

Here is an example of the tab with the raw data.

 Date Part# Part Description Rec’d Qty Parts Cost 01-20-2021 200717 SEAL KIT 1.00 \$64.10 09-29-2021 200717 SEAL KIT 1.00 \$72.69 01-18-2021 200957 SEAL KIT 0.00 \$117.52 02-03-2021 200957 SEAL KIT 1.00 \$117.52 02-23-2021 200957 SEAL KIT 1.00 \$117.52 01-18-2021 200965 BUSHING 1.00 \$128.19 02-24-2021 200965 BUSHING 1.00 \$128.19 01-03-2022 201004 PIN M100XY 1.00 \$1,561.33 01-10-2022 201004 PIN M100XY 1.00 \$1,561.33 01-03-2022 201005 BUSHING 2.00 \$413.64 01-10-2022 201005 BUSHING 2.00 \$413.64 01-29-2021 202637 PIN H, G CY 1.00 \$1,709.24 01-29-2021 202638 BUSHING 2.00 \$512.78

Here is an example of what I would like to pull from the raw data.

 Part# Part Description Start Price Highest Price Price Difference 200717 SEAL KIT 200957 SEAL KIT 200965 BUSHING 201004 PIN M100XY 201005 BUSHING 202637 PIN H, G CY 202638 BUSHING

Any help would be appreciated, thank in advance.

2 Replies

# Re: Finding the low and high price of a part #

In cell C19 in the attached example:

=INDEX(\$E\$2:\$E\$14,MATCH(A19&SMALL(IF(\$B\$2:\$B\$14=A19,\$A\$2:\$A\$14),1),\$B\$2:\$B\$14&\$A\$2:\$A\$14,0),0)

In cell D19 in the attached example:

=LARGE(IF(\$B\$2:\$B\$14=A19,\$E\$2:\$E\$14),1)

Maybe with the above formulas. Enter formulas as arrayformulas with ctrl+shift+enter if you don't work with Office365 or 2021. If you work with Excel 2019 or a newer version you might apply MAXIFS and MINIFS formulas.

best response confirmed by Nick_Depratto (New Contributor)
Solution

# Re: Finding the low and high price of a part #

Thanks @Quadruple_Pawn. The Minifs/Maxifs worked great.