Forum Discussion

Copper Contributor

Sep 09, 2022

Solved

Table Header Lookup

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 quote...

excel

Riny_van_Eekelen
Sep 09, 2022
JonHowarth Perhaps a little bit of clean-up with Power Query (integrated in Excel 2016 and newer) and a pivot table could do all of this without complicated formulas. See the attached file for an example.

Not sure though if this works in the 2016 version, as I'm loading data directly into the pivot table. Perhaps you need to go via an regular Excel table. Give it a try. May seem haunting at first but it's really not difficult.

More about PQ in the link below.
https://exceloffthegrid.com/power-query-introduction/

johnhowarth.xlsx21 KB

Riny_van_Eekelen

Platinum Contributor

Sep 09, 2022

JonHowarth Perhaps a little bit of clean-up with Power Query (integrated in Excel 2016 and newer) and a pivot table could do all of this without complicated formulas. See the attached file for an example.

Not sure though if this works in the 2016 version, as I'm loading data directly into the pivot table. Perhaps you need to go via an regular Excel table. Give it a try. May seem haunting at first but it's really not difficult.

More about PQ in the link below.

https://exceloffthegrid.com/power-query-introduction/

johnhowarth.xlsx21 KB

JonHowarth

Copper Contributor

Sep 14, 2022

This actually worked perfectly, your query didn't load properly in 2016 but it was enough that I could remake it in my sheet. It's a bit fiddly choosing the postcode through the pivot table but I can make this easier using splicers.

Thanks for the help!