Forum Discussion
IFS formula with multiple column lookups
I am trying to do a lookup in one column and return a value from one of five other columns based on the lookup value. The "Score in Dept" should return the value based on "Dept Collapsed". So in the first row, the Score in Dept for Assembly should be 2, Weld score is 3, Paint score is 1, etc.
| Dept Collapsed. | Assembly Score | Machining Score | Paint Score | Stamping Score | Weld Score | Score in Dept |
| Assembly | 2 | 3 | 2 | 2 | 3 | |
| Weld | 3 | 3 | 3 | 3 | 3 | |
| Paint | 2 | 1 | 1 | 1 | 2 | |
| Weld | 4 | 3 | 3 | 3 | 4 | |
| Machining | 2 | 2 | 2 | 3 | 2 |
I have this formula from an old spreadsheet (I didn't create it) and it should work, but doesn't.
=IFERROR(IF(IFS([@[Dept Collapsed]]="Assembly",[@[Assembly Score]],[@[Dept Collapsed]]="Machining",[@[Machining Score]],[@[Dept Collapsed]]="Paint",[@[Paints Score]],[@[Dept Collapsed]]="Stamping",[@[Stamping Score]],[@[Dept Collapsed]]="Weld",[@[Weld Score]], IFS([@[Dept Collapsed]]="Assembly",[@[Assembly Score]],[@[Dept Collapsed]]="Machining",[@[Machining Score]],[@[Dept Collapsed]]="Paint",[@[Paints Score]],[@[Dept Collapsed]]="Stamping",[@[Stamping Score]],[@[Dept Collapsed]]="Weld",[@[Weld Score]],"")))
Thank you in advance for your help
Hi
I would propose a different approach. See uploaded file.
