Index/Match function

[derivative_x]

Hello!

I'm trying to avoid writing a lengthy If() function for a spreadsheet, and I was wondering if an INDEX( MATCH() ) combination would help here. I also entertained the VLOOKUP() possibility, but that's not doing so well for me

In cell B23 I have a Data Validation drop down with 3 choices: Low Case, Base Case, and High Case.

When I drop the menu down, I want to match each case with each Row Title (in column B) with each month (Row 29 or Row 5)...that was one of the parts I was confused on

So for example, When I select High Case in B23 I want it to be able to pull up the numbers from C19:O19, and if I choose Low case from that same drop down menu it should pull C9:O9

I figure if I was going to incorporate INDEX(MATCH()) in this sheet it would like:

IF(B23="Low Case", INDEX(A5:O20, MATCH(), 0, IF(B23="Base Case",.....


I attached the actual spreadsheet Im working on to this file

Any Suggestions?

Thanks,
dx

[FDibbins]

not sure where you want the answer, but use this, copied across...

=INDEX($A$5:$O$20,MATCH($B$23,$A$5:$A$20,0)+1,MATCH(C$29,$A$5:$O$5,0))
increase the +1 if you want the next row down

[XOR LX]

Hi,

I agree that this could be done with VLOOKUP or INDEX/MATCH, and FDibbins' solution works perfectly, but since, for each month, your data is arranged such that your desired result lies in the same column as the data from which you are choosing, I don't see why you need to go to such lengths. This simple IF statement in cell C32 (presume these are the values you're looking for - you don't say) and copied across will do the trick:

=IF($B$23="High Case",C19,IF($B$23="Base Case",C14,IF($B$23="Low Case",C9)))

And calling that 'lengthy' would be a bit harsh!

[Melvinrobb]

I would not call the IF satement lengthy, but is definitely less versatile. If you added in 4 or 5 or 100 more cases, or if you rearranged the order of the cases, etc... you will run into issues.
If you understand INDEX, it is your better bet.

I would go with the following formula, but... there is no right answer. Just options.
=INDEX(C8:C20,MATCH($B$23,$A$8:$A$20,0)+1,1)

[derivative_x]

These both work well.
I'm using FD's formula for the top, but I can use XOR's formula for my expenses....all 54 flavors

[FDibbins]

Happy to help and thanks for the feedback