Jul 11 2019 03:02 AM
Hello,
I am familiar with VLookup and it's basic functions, however I am looking for someone that could help with the follows.
The attached is the basic premis for the spreadsheet, one sheet is the invoicing information the other tab is the cost for the drops by the haulier.
The formula or query I am hoping for is the VLookup or Equivalent to read multi pieces of data and return the price for the drop.
Columns C (Haulier), E (Town) and K (Truck)
What I am hoping it would do is Read the haulier, match the town and truck, then in column J (Cost) pull through the cost from the 2nd tab to match this.
The reason this is complicated is due to having two hauliers, I only need it to read the one haulier being "A Roberts", which is their data on the 2nd tab.
Jul 11 2019 03:16 AM
Hi
Try INDEX and MATCH. You can use them both.
You can also plug your data-set into Power Pivot enabling DAX and use the RELATED function.
Hope that help
Jul 11 2019 03:21 AM
As variant
=IFERROR(INDEX(Haulier!$A$1:$F$76,MATCH($E2,Haulier!$A$1:$A$76,0),MATCH($K2,Haulier!$A$1:$F$1,0)),"no such")
Please see in column O attached
Jul 11 2019 03:33 AM
Thank you for getting back to me.
This seems to be along the right lines, is there a way of doing as a range on the columns, rather than a specific cell, please.
Jul 11 2019 03:54 AM
Thank you for getting back to me.
Please could you possibly provide an example? As I am struggling to match the Array against the lookup, for it to return a result.
Jul 11 2019 03:59 AM