I’m trying to automatically assign a reviewer type (like “Peer” or “Leader”) based on a reviewer’s phone number. The problem is that in my reference sheet, the 20 possible reviewers are listed across columns — each one has a phone number and a reviewer type in a separate column. In my submission sheet, I just have one phone number per row, and I want a formula to check that number against all 20 reviewer phone number columns, and return the matching reviewer type
Smartsheet doesn’t support horizontal lookups across multiple columns, and it also limits formulas to 20 nested IF statements. That’s making it hard to build a single formula that checks all 20 reviewer columns and also checks for the reviewee’s phone number to return “Self Report.”I’ve tried using INDEX(MATCH()), but that only works with vertical (column-based) data. Since my reviewer info is horizontal, I’m running into parsing errors and nesting limits when trying to do everything in one formula.
I've censored out some info and highlighted the columns I'm focusing on.
