Forum Discussion
Index/Match/OR Formula
Hi, Looking for assistance if possible:
Data Table
| Year 22 | Year 23 | Year 24 | Year 25 | Year 26 | Year 27 | ||
| LEV A | 1 | 1,000 | 1,046 | 1,083 | 1,116 | 1,151 | 1,186 |
| LEV A | 2 | 2,000 | 2,092 | 2,165 | 2,232 | 2,302 | 2,373 |
| LEV A | 3 | 3,000 | 3,138 | 3,248 | 3,349 | 3,452 | 3,559 |
Current Index/Match Ref table
| Year | Year 22 | |
| Class | LEV A | |
| Step | 1 | $ 1,000.00 |
| New Step | 2 | $ 2,000.00 |
Formula using is: =INDEX(C:C,MATCH(1,($K$5=A:A)*($K$6=B:B),0)) (Step 1 Value)
=IFNA(INDEX(C:C,MATCH(1,($K$5=A:A)*($K$7=B:B),0)),0) (Step 2 Value)
Idea is to have Year; Class; Step and New Step as validated list - so that I change either Year/Class/Step/New Step using just that 1 table rather than 1 for each year - which the formula currently is doing. Looked at trying to add multiple index and OR (not very good at using OR).
Using Office 365.
Thanks for any input/guidance
2 Replies
=INDEX($C$2:$H$4,MATCH(1,($A$2:$A$4=$K$6)*($B$2:$B$4=K7),0),MATCH($K$5,$C$1:$H$1,0))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.
- Many thanks, OliverScheurich; that looks to do the trick; such subtle changes to the formula 🙂
I tried using C:H but dot not swap the matches around etc.
Much appreciated
