Struggling with VLOOKUP, INDEX and MATCH in Excel

Question

I have a small spreadsheet that is intended to display current freight costs per shipping merchant, by shipping port and by office location.

I also have a smaller summary table that displays per shipping port, the cheapest rate, the merchant offering it, and the location to which it applies.

The main table is administered by our end-users, but the smaller table should update itself based on the end-user's inputs.

The values in the red box are how it should look. I have managed to display the lowest rate in its row by using AGGREGATE.

But I cannot work out how to insert the corresponding depot and merchant into the summary in the same way. I've tried various combinations of INDEX, MATCH, VLOOKUP and HLOOKUP without success - it usually ends up with a circular reference somewhere.

L

Answer 1

Perhaps you could try using functions like INDEX() instead of using volatile functions.

A friendly suggestion

OFFSET() is a flexible function and it can point to a different-sized range that is calculated on-the-fly. It is a volatile function. If you go to a completely unrelated cell and enter a number, all of the OFFSET() functions will calculate—even if that cell has nothing to do with.

It has been found, Excel is very careful to spend time calculating the cells that needs to be calculated. But once you use the OFFSET(), all of the OFFSET() cells, plus everything downline from the OFFSET(), starts calculating after every change in the worksheet.

Why it matters ? INDEX() is not volatile. You enjoy all of the flexible goodness of OFFSET() without the time-sucking recalculations over and over.

---

Here is shown below, what you can try:

---

Getting the MIN values.

• Formula used in cell AB7

=AGGREGATE(15,6,(Q7:Z7)/ISNUMBER(Q7:Z7),1)

Note: I have used 15 which refers to SMALL instead of 5 with an ISNUMBER() Function which ignores all the text values as well as #N/A() as well. Thus, giving the Minimum values of each office location.

---

Getting the Merchant Name,

• Formula used in cell AC7

=INDEX(Q7:Z7,,MATCH(AB7,Q7:Z7,0)+1)

Note: The MATCH() returns the relative position of the MIN value column wise, and to get the Merchant we are simply adding 1 to it, and wrapping the whole within INDEX().

---

Getting the Depot Name

• Formula used in cell AD7

=INDEX($Q$5:$Z$5,,MATCH(AB7,Q7:Z7,0))

Note: Same as above the MATCH() returns the min value position, and then we are wrapping it within INDEX() to get DEPOT for the respective MIN values.

Also, here for the Depot cells I have not used Merge & Center instead have used the Alignment as Center Across Selection, that way it much easier to use a formula and not resorting to complicated ones. Avoid using Merge & Center .

Note: All the above formulas used, you need to fill down, and it has been assumed you may not have duplicate Min values. If you get duplicate values for Rates then the formulas for Merchant & Depot will change.

Answer 2

You can use INDEX/MATCH wrapped with OFFSET to help you achieve this. This is assuming that merchants are never numeric.

Cell M3 (min) formula:

=MIN(B3:J3)

Cell N3 (merchant) formula, which locates the min(rate) in that row and returns the value 1 column to the right of it:

=OFFSET(INDEX(B3:K3,MATCH(MIN(B3:K3),B3:K3,0)),,1)

Cell O3 (depot) formula, which locates the min(rate) in that row and returns the first row in that column. Or more precisely, x-nbr of rows above it.

=OFFSET(INDEX(B3:K3,MATCH(MIN(B3:K3),B3:K3,0)),-ROW(A2),)