Need help with Excel formula VLOOKUP

EddScott · 13 Sep 2011 at 09:49

I understand that in Excel you can only have 7 IF functions in a formula?

What happens if I have need more than 7 IFs ? Can I use VLOOKUP ?

Cell A1 could have up to 50 different codes (IR1, IR2 etc) and depending on which code is in the cell would determine in what manner a figure is split up.

So if A1 has the code, B1 has the start value and C1,C2,C3 all have different finishing values depending on the calculation determined by what code is in A1.

So If A1 say code "IR1" and B1 has £100 - IR1 requires the £100 to be split 20%, 10%, 70% and the percentages would appear in C1, C2, C3.

Can I use VLOOKUP for this? If the first column in the VLOOKUP table is the code and the 2nd column in VLOOKUP is the required calculation.

Any help really appreciated!

Sin_Chase · 13 Sep 2011 at 10:18

VBA it

Linkex · 13 Sep 2011 at 14:05

Here's how I would do it - Start by creating a reference table to determine what your percentages are. I've done it on the same sheet below (the table on the right), but you could use a hidden sheet for this part.

Then, on the table on the left, I've used a simple vlookup to get the result I need for each price.

If we take C3 as an example, I'll explain whats happening. First, ignore the "$" signs. These just tell excel that the following character should not automatically be changed if I duplicate the cell somewhere else.

So, ignoring them, A3 looks like this:
=B3*vlookup(A3,G3:J6,3,0)

=The amount you need to divide up
Tells the forumla what to search for (IR1, IR2, IR3 etc
This is the area of the spreadsheet it searches in. This should be the entire area of your "reference table"
This is the number of the colomn you want to return a result for. *Note* - This is relative to the "range" you set, not the actual cell number. If my range is G-J, then column G is "1", column H is "2" and so on
This tells the vlookup to only show a result on an exact match. If you set it to 1, it uses a non-exact method)

You can see the way it works is by multiplying the initial value by the amount in the reference table, so your figure should be 0.XX where XX is the the percentage of the initial value you want to show. So if you want 20%, set it to 0.20, if you want 30%, its 0.30 etc etc

EddScott · 13 Sep 2011 at 15:55

Wow, that is spot on!!

Many thanks mate

EddScott · 14 Sep 2011 at 13:16

Linkex - can I call on your knowledge one more time?

Is it possible to add 3 more columns next to the figures which provide the % in the form of 10%, 20%, 30%?

So in the top table, where it has £10 £10 £70 can I include £10 10% £10 10% £70 70% as 3 extra columns - all changing in relation to the IR code same as the figures.

I've been fiddling with it and can't make it work.

Again, all help much appreciated!

EDIT - Aha, got it. Thanks again anyway