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

Jason Getty

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.

SS INDEX COLLECT FORMULA EXAMPLE_1.pdf

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.