Nesting Index & Match together

[BBFDSue]

So I have a matrix that I'm trying to populate in a worksheet. I'm getting the #Value error.

Here is my formula =INDEX(E:E,MATCH(H3&J4&I5&H5,A:A&B:B&C:C&D:D,0))

I thought the Index/Match function would allow me to be a little 'all over the place' with my matrix box qualifying cells. Am I mistaken?

Attached is a snapshot and sample workbook. In the real worksheet the matrix will be on a different tab and then the resulting figure will be used in other formulas.

[ByteMarks]

You could try an array formula. Press Ctrl+Shift+Enter after putting it in the formula bar

Please Login or Register  to view this content.

[protonLeah]

Named ranges created:
Carrier =Sheet1!$C$2:$C$193
Destination =Sheet1!$A$2:$A$193
Origin =Sheet1!$B$2:$B$193
Product =Sheet1!$D$2:$D$193
Rate =Sheet1!$E$2:$E$193

J5:

Please Login or Register  to view this content.

[BBFDSue]

That works wonderfully, however using the array makes it hard (impossible really for me anyway) to nest it as part of a larger formula. The formula in it's entirety would be:

((Carrier Cartgage*Fuel Surcharge Percentage)+Carrier Cartage)+CPL . Example in numbers ((.0484*22.18% = 0.01073 )+ .0484) + 1.67 = 1.729135

What you've given me (beautifully I might add) is only the .0484 part - the Carrier Cartgage part. The other figures will be pulled from other cells that I already have populated with the necessary Fuel Surcharge/CPL. But they do change so they aren't a constant value.

I can add another tab and do the math in stages, but I was hoping to simplify it into a single worksheet with just the carrier rates on a separate tab.

Thoughts? I am really weak with the arrays.

[BBFDSue]

Holy Smoking Jones on Fire...... ProtonLeah

That works and it allows the rest of my formula to flow. Literally, first try... down to the forth decimal perfect.... I just made a coworker dance with me.

Thank you. Thank you. Thank you. THANK YOU.