VLookup or Equivalent

Copper Contributor

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

@AdamUKSaint 

 

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 

@AdamUKSaint 

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

@Sergei Baklan 

 

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.

 

 

@Theo_Bear 

 

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.

Actually on second glance, I can see what you have done and breakdown the formula.

Thank you for your help.

@AdamUKSaint , good, you are welcome