Use Sumifs and Index Match to add values in a multiselect lookup table

Hi there,

I've followed some input (as best I could figure out) from another help column to attempt to add up analyses costs from a multiselect column. I'll add pictures as best I can.

Process for use of sheet:

1.) Enter sample ID into a database / flat file, called "Test Tracker"

Image of the table "Test Tracker"

2.) Select analyses from multiselect column (Test Name), also on the sheet "Test Tracker"

3) ... the bit I can't do - a formula adds up the values of the analyses selected in a cost cell. The values of the analyses are in a lookup table called "Test Costs".

Image of the table "Test Costs":

The formula I have can successfully look up one value (like a vlookup) but generates the fault #NO MATCH if I select multiple tests.

This formula look like this:

=SUMIFS({Test Costs Range 2}, {Test Costs Range 3}, CONTAINS(@cell, INDEX({Test Costs Range 1}, MATCH([Test Name]@row, {Test Costs Range 3}, 0), 1)))

I'd REALLY appreciate any thoughts / assistance / input please!



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!