Version: Excel 2016 (Windows 10)
A small project that has turned into a big task and I'm now too emotionally invested in to give up!
I'm building a tool that looks in a table of haulage quotes for different postcodes to find the cheapest quote depending on the entered postcode area. In the real sheet, the Postcode area is populated by a query looking into three other sheets to append delivery postcodes into one long list, and only displaying the first two letters (this cuts down on the amount of entries). The quote cells are all Vlookups into another sheet.
Part 1 - cheapest cost). The idea is someone can enter a postcode in cell K4, then cell K5 shows the cheapest quote, and cell K6 shows which haulier it is - which is the header for each column. My formula in cell K5 seems to half work. It does show the cheapest price if one of the duplicate postcode rows has all values above zero. (As shown with a postcode starting with CO, but if you use BB it shows a zero). So if all the rows have at least one zero value, then it always returns zero.
Part 2 - show column header). I've completely blanked on this part. I've tried using Index/Match and Xlookup formulas but it always returns #Value. I want cell K6 to show "LIV LS" if the cheapest cost is in that column.
Formula in cell K5:
=MIN(MINIFS(lookup_table[FXT LS],lookup_table[Postcode Area],LEFT(K4,2),lookup_table[FXT LS],">0"),MINIFS(lookup_table[SOU LS],lookup_table[Postcode Area],LEFT(K4,2),lookup_table[SOU LS],">0"),MINIFS(lookup_table[LIV LS],lookup_table[Postcode Area],LEFT(K4,2),lookup_table[LIV LS],">0"),MINIFS(lookup_table[FXT CS],lookup_table[Postcode Area],LEFT(K4,2),lookup_table[FXT CS],">0"),MINIFS(lookup_table[SOU CS],lookup_table[Postcode Area],LEFT(K4,2),lookup_table[SOU CS],">0"),MINIFS(lookup_table[LIV CS],lookup_table[Postcode Area],LEFT(K4,2),lookup_table[LIV CS],">0"))
It won't let me attach a file so here is the table.
The table is called "lookup_table", it needs to be for the formula above. This table actually has hundreds of entries in an ever growing list of area codes.
| Postcode Area | FXT LS | SOU LS | LIV LS | FXT CS | SOU CS | LIV CS |
| AB | £ 2,386.50 | £ 2,416.60 | £ 1,500.70 | £ - | £ - | £ - |
| AB | £ 2,300.00 | £ 2,400.00 | £ 1,500.00 | £ - | £ - | £ - |
| AL | £ 450.00 | £ 450.00 | £ 855.70 | £ - | £ - | £ - |
| B8 | £ 750.00 | £ 550.00 | £ 550.00 | £ - | £ - | £ - |
| B9 | £ 743.90 | £ 576.20 | £ 520.30 | £ - | £ - | £ - |
| BB | £ 1,238.40 | £ 1,122.30 | £ 450.00 | £ - | £ - | £ - |
| BB | £ 1,200.00 | £ 1,100.00 | £ 450.00 | £ - | £ - | £ - |
| BB | £ 1,100.00 | £ 1,000.00 | £ 450.00 | £ - | £ - | £ - |
| BB | £ 1,100.00 | £ 1,100.00 | £ 450.00 | £ - | £ - | £ - |
| BD | £ 950.00 | £ 1,000.00 | £ 450.00 | £ - | £ - | £ - |
| BD | £ 950.00 | £ 1,000.00 | £ 450.00 | £ - | £ - | £ - |
| BL | £ 1,094.35 | £ 1,032.00 | £ 450.00 | £ - | £ - | £ - |
| BN | £ - | £ - | £ - | £ - | £ - | £ - |
| BN | £ 574.05 | £ 450.00 | £ 1,176.05 | £ - | £ - | £ - |
| BN | £ 650.00 | £ 450.00 | £ 1,100.00 | £ - | £ - | £ - |
| BN | £ 642.85 | £ 450.00 | £ 1,242.70 | £ - | £ - | £ - |
| BS | £ 1,000.00 | £ 450.00 | £ 850.00 | £ - | £ - | £ - |
| C0 | £ 450.00 | £ 650.00 | £ 1,100.00 | £ - | £ - | £ - |
| CB | £ 450.00 | £ 608.45 | £ 866.45 | £ - | £ - | £ - |
| CM | £ 450.00 | £ 528.90 | £ 1,023.40 | £ - | £ - | £ - |
| CM | £ 450.00 | £ 614.90 | £ 1,051.35 | £ - | £ - | £ - |
| CM | £ 450.00 | £ 450.00 | £ 950.00 | £ - | £ - | £ - |
| CO | £ 450.00 | £ 640.70 | £ 1,062.10 | £ - | £ - | £ - |
| CO | £ 450.00 | £ 707.35 | £ 1,124.45 | £ - | £ - | £ - |
| CO | £ 450.00 | £ 650.00 | £ 1,000.00 | £ 370.00 | £ 780.00 | £ 1,025.00 |
| CV | £ 718.10 | £ 516.00 | £ 602.00 | £ - | £ - | £ - |
| CV | £ 623.50 | £ 571.90 | £ 546.10 | £ - | £ - | £ - |
| CV | £ 750.00 | £ 550.00 | £ 550.00 | £ - | £ - | £ - |
| CW | £ 933.10 | £ 838.50 | £ 450.00 | £ - | £ - | £ - |
| CW | £ 930.95 | £ 851.40 | £ 450.00 | £ - | £ - | £ - |
| DD | £ 2,171.50 | £ 2,214.50 | £ 1,292.15 | £ - | £ - | £ - |
| DD | £ 2,000.00 | £ 2,100.00 | £ 1,100.00 | £ 1,940.00 | £ 3,000.00 | £ 1,215.00 |
| DE | £ 782.60 | £ 771.85 | £ 450.00 | £ - | £ - | £ - |
| DE | £ 778.30 | £ 683.70 | £ 450.00 | £ - | £ - | £ - |
Here is what the sheet looks like.
Accepted Solutions