Forum Discussion
VLookup or Equivalent
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.
6 Replies
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
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.
- Actually on second glance, I can see what you have done and breakdown the formula.
Thank you for your help.
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
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.
