Help with looking up lowest value

Provided your prices are numbers formatted as currency (i.e. you haven't typed prices like "$10" in the celsl say), you can use the Min (minimum) function to refer to the prices in another sheet, as follows...

Click in the desired cell in sheet 2 and type just

=Min(

then (before pressing another key) click on the Sheet 1 tab then click your cursor in the first price cell (say B2 and drag it over the other price cells say C2 and D2 - a dotted line will shimmer around them- then just type a left bracket ) and the formula bar will show something like:

=Min(Sheet1!B2:D2)

and press enter. That's it, the cell in sheet 2 should show the lowest value from the range in sheet 1.

It can also work if you have numbers on more than one line. 

This is a job for INDEX/MATCH. The generic INDEX formula looks like this:-

=INDEX([Range], [Row number], [Column number])

You specify a range and then, within this range, numbers to identify row and column.

=INDEX(B3:K30, 3, 7)

In this example the range is B3:K30. The 3rd row is sheet row 5 (because the range starts in row 3) and the column is H which is the 7th column counting from B3. So, =INDEX(B3:K30, 3, 7) is just a complicated way of saying =H5.

What makes this method interesting is that you can not only calculate (or reference) row and column numbers but also omit them. INDEX(B3:K30, 3) would return the entire 3rd row of the range and =MIN(INDEX(B3:K30, 3)) would return the lowest value in that range, such as your lowest price.

The MATCH function just looks for a value in a range (one row or one column). It's much like VLOOKUP or HLOOKUP) but returns the position of the found match instead of a value. The generic syntax is simple.

=MATCH([Lookup Value], [LookupRange], [Match Type]

Match Type can be 1, 0 or -1 describing two kinds of approximations and "exact match", which is specified with 0. Accordingly, the real formula below would look for the value found in A1 in the range A3:A30 and return its row number.

=MATCH(A1, A3:A30, 0)

Note that the row number will be the range row, not the sheet row. Therefore, if the function returns 3 that will be sheet row 5 because the lookup range starts in sheet row 3. In practise, you never need to know but you must pay attention during setup.

Putting it all together, you use the MATCH function to find  the row number for the INDEX function.

=MIN(INDEX(AllPrices, MATCH(A1,Parts,0),0))

Here I used 2 named ranges.

• AllPrices is the range B3:K30 in my examples above. It comprises all customer price columns in your data but excludes the column with the part numbers (which you would include for VLOOKUP).
• Parts is the range A3:A30 in my above examples. In your workbook it's the column with the part numbers.

You can replace the names in the formula with range addresses but names are better not only because meaningful names explain the formula but also because it's easier to make them dynamic and easier to implement changes: you never have to change the formulas if changes occur to the ranges.

The important thing is to let both ranges have exactly the same number of rows and let both start at the same sheet row number.