Trying to make my formula shoter

[Deadpool]

I am using excel 2003 and I am trying to make a formula shorter since 2003 only allows 7 if statements I need to add 2 more.

Here is the formula

=IF($G$137={"Minion","Minions"},VLOOKUP($H$133,$F$155:$P$159,$H$134+1,FALSE),IF($G$137={"Brute","Brutes"},VLOOKUP($H$133,$F$167:$P$171,$H$134+1,FALSE),IF($G$137={"Soldier","Soldiers"},VLOOKUP($H$133,$F$179:$P$183,$H$134+1,FALSE),IF($G$137={"Archer","Archers"},VLOOKUP($H$133,$F$179:$P$183,$H$134+1,FALSE),IF($G$137={"Skirmisher","Skirmishers"},VLOOKUP($H$133,$F$191:$P$195,$H$134+1,FALSE),IF($G$137={"Boss","Bosses"},VLOOKUP($H$133,$F$203:$P$207,$H$134+1,FALSE),IF($G$137={"Unique Monster","Unique Monsters"},VLOOKUP($H$133,$F$215:$P$219,$H$134+1,FALSE)," ")))))))

Thanks for any help given.

 

[Jonmo1]

Are you sure that formula actually works as it is?

I don't see how the IF's can work
IF($G$137={"Minion","Minions"}

Is that supposed to be saying if G137 = Minion OR Minions ??
That's now how Excel will process it.
It will only consider if it = Minion. It will ignore Minions.

For that to work it would have to be written like
IF(OR($G$137={"Minion","Minions"})


Anyway, it looks like you really only need to adjust the range of the vlookup based on the value of G137. Should be doable.
Standby

 

[HackSlash]

This looks like a job for VBA! The evaluation you are performing is a perfect example of the Select Case statement.

https://msdn.microsoft.com/en-us/library/office/gg278665.aspx

 

[Deadpool]

I know nothing about VBA no experience with it at all.

 

[Deadpool]

Each of the types have there own tables that look up the quantity of them based on two factors. 1. Player level. 2. Number of players. I trying to make the vlookup table array variable so that if the cell G137 said minion the it would look up the minion table and get the number from there. If it said brute then it would look at another table. I hope that makes sense.

 

[Jonmo1]

Try

=VLOOKUP($H$133,OFFSET($F$155:$H$159,(MATCH(SUBSTITUTE($G$137,"Soldier","Archer")&"*",{"Minions";"Brutes";"Archers";"Skirmishers";"Bosses";"Unique Monsters"},0)-1)*12,0),$H$134+1,FALSE)

 

[Deadpool]

It gave me a reference error. Is the a way to make the table array in red variable based on the selection in lets say cell A1 minion/brute/archer/soldier/boss/etc... =VLOOKUP(F137,$A$153:$B$252,2,FALSE)

<colgroup><col></colgroup><tbody>
</tbody>