MATCH MULTIPLE CRITERIA ACROSS MULTI-COLUMN RANGES RETURN VALUE FROM ANOTHER COLUMN

Hello,

I am trying to look up an Employee from one column on one sheet and match it from a range on another sheet. Once matched, need to look up another specified criteria on another range which looks at the same column # in an identical column count range. Finally, look up another specified criteria at the same column # in an identical column count range and if all match, return the value from the same column # in another range with an identical column count. See attached file and picture diagram.



Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @Jason Getty, without getting into the nitty gritty, I would lay these out in multiple columns, at least at first. The first column would do your first lookup. Your second column would use the first column as a reference to do the next lookup. Continue that pattern, because it's highly likely you'll end up troubleshooting and it's best to do that piecemeal. Then, if you really need to put them in one cell, start back in the first column and copy that entire formula into the the next column, replacing the @row reference you were using to reference the first column with the entire formula from the first column. Repeat on down the columns.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!