# Trying to make my formula shoter

##### New Member
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.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Jonmo1

##### MrExcel MVP
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

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

##### New Member
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

##### MrExcel MVP
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)

##### New Member
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>

Replies
4
Views
956
Replies
0
Views
271
Replies
17
Views
613
Replies
10
Views
122
Replies
3
Views
2K

1,191,204
Messages
5,985,278
Members
439,953
Latest member
suchitha

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back