I am working on a pricing calculator to pull specific rates from a second sheet.
First Sheet:
Second sheet:
I used a variation of this formula but it's showing a #SPILL error
=IFERROR(INDEX('SHEET2!B2:F6,MATCH(SHEET2!B2:F2,0),MATCH)SHEET2!A1:A5,0)+1)*A1,0)
Any help is much appreciated. Thank you!
First Sheet:
Name | Rate |
This is a drop down that shows the list of names from the second sheet (B2:F2) | This is where the formula should go = Rate associated with Name selected in the drop down x A1 (quantity). if it helps service line is in cell A2 |
Second sheet:
Service Line | Kelly | Lori | Lisa | Johnson | Dawn |
1 Day | 2.85 | 3 | 3.5 | 1 | 2.25 |
2 Day | 5.70 | 6.30 | 3 | 1 | 4 |
3 Day | 5.42 | 5.40 | 3 | 1 | 5 |
4 Day | 5.22 | 5.10 | 3 | 1 | 6 |
5 Day | 4.82 | 5.50 | 3 | 1 | 7 |
I used a variation of this formula but it's showing a #SPILL error
=IFERROR(INDEX('SHEET2!B2:F6,MATCH(SHEET2!B2:F2,0),MATCH)SHEET2!A1:A5,0)+1)*A1,0)
Any help is much appreciated. Thank you!