After a lot of brainstorming I leared to write this forumula and it actually worked, however, when I tried to drag it down it did not work on some of the random cells in sheet and gave #REF error. I am not able to understand that when the value is there and the formula is working oo other cells then why on dragging it did not work on few.
Please helpm thanks in advance
Here is the formula
=INDEX(Volume!$D$3:$F$68,MATCH(B23,Volume!$A$3:$A$68,0),MATCH(D23,Volume!$D$2:$F$2,0),MATCH(E23,Volume!$B$3:$B$68,0))
Attached is the link to access Excel sheet because samme formula on dragging miss some of the random cells and shows #REF.
It looks like you're trying to check three columns with an INDEX formula but your fourth argument is wrong (the 'MATCH(E23,Volume!$B$3:$B$68,0)' part). This argument selects which range of data should be used but you're giving it a value outside the range of data e.g. that part of the formula in cell H23 on the 'Sea Freight Rates' sheet returns 24 but there's no 24th column of data you've specified. The formula only works in some cases by pure accident and because of how the data on the 'Volume' sheet is laid out.
This is the formula that you want to use in cell H2 and then drag down. The 'MATCH($B2&$E2' part joins the value in column B and E together to give a unique combination (e.g. 'CNNBO PT20DC' for cell H2) while the 'Volume!$A$2:$A$68&Volume!$B$2:$B$68' part produces an array of all possible combinations from the relevant columns in the 'Volume' sheet. It will look for 'CNNBO PT20DC' in this array and return the relevant row number. The formula then looks for the correct 'Destination Port' like you have before which gives the relevant column number. You'll then get the correct value and that row and column:
=INDEX(Volume!$D$2:$F$68,MATCH($B2&$E2,Volume!$A$2:$A$68&Volume!$B$2:$B$68,0),MATCH($D2,Volume!$D$2:$F$2,0))
Side-note, you have two rows for INNSA PT and 20DC on the 'Volume' sheet (rows 6 and 22) - is that correct?