Help with index and / or match formula with multiple match required to return a value

Please help.

See attached file, "Rate Sample Index-Match Formula".

I need a formula to return the value at the cross section of two (2) lookup values that match. This formula will be input into column D under, "RATE" on the 1st tab, "TEST FILE".

In the 1st tab, "TEST FILE" there are a series of columns as follows;

A = Service
B = From
C = To
D = Rate

In the 2nd tab, "RATES" there is a series of rates with drivers From (green) & To (blue)

The formula needs to do the following;

1. Lookup the "From" value in column B on tab, "TEST FILE" and match to column B2:B59 on tab, "RATES" both highlighted in green
2. Then Lookup the "To" value in column C on tab, "TEST FILE" and match to row C1:BH1 on tab, "RATES" both highlighted in blue
3. Then return the value at the cross section of the match "From" (point 1 above) & "To" (point 2 above) in range C2:BH59

For Example;

The rate From SYD To CBR = 0.33. I have highlighted this in yellow on both tabs to show where the formula needs to lookup the data to return the answer

Please provide the formula to perform the above.

Additionally, if we were to add service as an additional lookup match how would this work?

Thanks in advance!!!!

Originally Posted by sktneer

Try this......

Please Login or Register  to view this content.

Reply

Perfect!!! That works awesome - thank you so much!!!!

I have columns with the following

REACTION TIMES.jpg
i want to know if a type of complaint fall within or above the baseline reaction times for the different type of complaints, the answer must be in the target column with the answers within or above the baseline of type of complaints

will able to help me

mrs coetzer