Selected Answer

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.