How to I Convert a "Too Long" IF statement into a V-Lookup?

[RBusiness]

Hello everyone,

I was very excited when I made a huge IF statement that would help me edit values based on original values. For example
IF P1 >0, P1<10,E1
IF P1 >10,P1<20,E1-1
IF P1 >20,P1<40,E1-3

Basically as the value of P1 increases from tier to tier, I reduce the value of E1 by a fixed amount. The increments are not always sequential meaning they will always go up, but not like '1,2,3,4,5', more like '1,2,4,10,12,100'.

So after trying to paste it in a cell, Excel popped up a message that said my Formula was too long. By the looks of it, I agree so how can I use this formula in a V-Lookup? My formula is below I simply don't know how to make this into a v-lookup. Thank you.

MY IF STATEMENT

=IF(AND(P1>0,P1<10),E1,IF(AND(P1>9.99,P1<15),E2-1,IF(AND(P1>14.99,P1<20),E3-2,IF(AND(P1>19.99,P1<25),E4-5,IF(AND(P1>24.99,P1<30),E5-7,IF(AND(P1>29.99,P1<40),E6-8,IF(AND(P1>39.99,P1<50),E7-9,IF(AND(P1>49.99,P1<60),E8-12,IF(AND(P1>59.99,P1<70),E9-20,IF(AND(P1>69.99,P1<80),E10-30,IF(AND(P1>79.99,P1<90),E11-40,IF(AND(P1>89.99,P1<100),E12-50,IF(AND(P1>99.99,P1<110),E13-55,IF(AND(P1>109.99,P1<130),E14-60,IF(AND(P1>129.99,P1<150),E15-70,IF(AND(P1>149.99,P1<175),E16-80,IF(AND(P1>174.99,P1<200),E17-90,IF(AND(P1>199.99,P1<250),E18-100,IF(AND(P1>249.99,P1<275),E19-120,IF(AND(P1>274.99,P1<300),E20-170,IF(AND(P1>299.99,P1<350),E21-175,IF(AND(P1>349.99,P1<400),E22-200,IF(AND(P1>399.99,P1<450),E23-225,IF(AND(P1>449.99,P1<500),E24-250,IF(AND(P1>499.99,P1<600),E25-300,IF(AND(P1>599.99,P1<700),E26-350,IF(AND(P1>699.99,P1<800),E27-400,IF(AND(P1>799.99,P1<1000),E28-500,IF(AND(P1>999.99,P1<1200),E29-600,IF(AND(P1>1199.99,P1<1400),E30-700,IF(AND(P1>1399.99,P1<1600),E31-800,IF(AND(P1>1599.99,P1<1800),E32-900,IF(AND(P1>1899.99,P1<30IF(AND(P1>2999.99,P1<5500),E34-1400,))))))))))))))))))))))))))))))))))

 

[pplstuff]

You need to make a table with the values first:
P1Value|Increment
10|0
20|1
30|3
...

Then use vlookup off of this table. There are some excellent tutorial videos on the youtube channel "excelisfun" if you're unsure how vlookup works.

 

[RBusiness]

the value of 10 is actually a range of 10.00 to 20.00 which would trigger a calculation. I looked at youtube and didn't find an example that is similar to running calculations on V-Lookup.

 

[rs2k]

Have you considered 'select case'?

 

[Jeff Meyers]

Try this formula in Cell Q1

Note that this is not a "complete" table with all of your conditions - continue it in the same manner for the remainder of your conditions / tests. And expand the formula ranges accordingly.

Sheet3[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]O[/TH]
[TH]P[/TH]
[TH]Q[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD="align: right"]0[/TD]
[TD]E1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]59.99[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]10[/TD]
[TD]E2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]15[/TD]
[TD]E3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]20[/TD]
[TD]E4[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]25[/TD]
[TD]E5[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]30[/TD]
[TD]E6[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"]40[/TD]
[TD]E7[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]50[/TD]
[TD]E8[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD="align: right"]60[/TD]
[TD]E9[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD="align: right"]70[/TD]
[TD]E10[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD="align: right"]80[/TD]
[TD]E11[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD="align: right"]90[/TD]
[TD]E12[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD="align: right"]100[/TD]
[TD]E13[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD]------------[/TD]
[TD]------------[/TD]
[TD]------------[/TD]
[TD]------------[/TD]
[TD]------------[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]------------[/TD]
[TD]------------[/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
Q1	=INDIRECT(VLOOKUP(P1,$A$1:$C$13,2,TRUE))-VLOOKUP(P1,$A$1:$C$13,3,TRUE)

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[pplstuff]

One you understand vlookup() the table will make sense, it will look for values less than 10, then less than 20, then less than 30, etc. Vlookup will return a single value, so your end formula will look like "=e1-vlookup()".

 

[Jeff Meyers]

Note that my solution above (Post #5) was based off of your CODE posted IF statement with more than one possible cell being reduced (i.e. not always E1) depending on meeting the > / < tests.

If it is always cell E1 that is being reduced then it is even simpler as pplstuff described in Post #6.

I guess that I should have asked for clarification.

 

[Jeff Meyers]

If the "reduced cell" will always be Cell E1 then try this.

Again, this is only a sample section of the solution - continue this pattern for all remaining criteria / conditions.

Excel Workbook
	A	B	C	D	E	F	O	P	Q
1	0	0			500			59.99	488
2	10	1
3	15	2
4	20	5
5	25	7
6	30	8
7	40	9
8	50	12
9	60	20
10	70	30
11	80	40
12	90	50
13	100	55
14
15
16	------------	------------	----	----	------------	----	----	------------	------------
17
Sheet4

Excel 2010

Cell Formulas
Range	Formula
Q1	=E1-VLOOKUP(P1,$A$1:$B$13,2,TRUE)

 

[RBusiness]

E1 represents the cell that's in the row 1! It will be copied down to 40,000 rows so if row 30000 is using vlookup it will be E30000.

Like this
IF the value of P1-P50000 is less than 10, take the value of E1-50000(the one in the same row) and put it here.
IF the value of P1-P50000 is between 10 and 20, take the value of E1-50000 (the one in the same row) and subtract 1 from E1-50000
IF the value of P1-P50000 is between 20 and 30, take the value of E1-50000 (the one in the same row) and subtract 2 from E1-50000
IF the value of P1-P50000 is between 30 and 40, take the value of E1-50000 (the one in the same row) and subtract 4 from E1-50000

And so on. Basically here's what I'm doing. I have a calculation that shows me the difference between two cells in a row. IF the value of is within various ranges, I subtract a digit or two depending on how much the value is. So IF "cell" is between 10 and 20 I refer to a different cell "i.e. E#" in the same row and use it to deduct from.

Which solution would be best? I tried one and didn't get any result from it.

Note: My understanding of Vlookup is that the table is on a separate worksheet which I've named "testingtable", then I understand that I take the Vlookup code and paste it into the column where the lookup / calculation is wanted.

 

[Jeff Meyers]

OK - got it now.

Give this solution a try. Your table (TESTINGTABLE) on one worksheet and your data on another.

Here's the TESTINGTABLE (expand it / adjust it as needed with additional values - both the lower end of ranges and your desired reduction values):

Sheet5[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[/TR]
[TR]
[TH]1
[/TH]
[TD="align: center"]Low End
[/TD]
[TD="align: center"]Reduce
[/TD]
[/TR]
[TR]
[TH]2
[/TH]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]3
[/TH]
[TD="align: right"]10
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TH]4
[/TH]
[TD="align: right"]15
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TH]5
[/TH]
[TD="align: right"]20
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TH]6
[/TH]
[TD="align: right"]25
[/TD]
[TD="align: right"]7
[/TD]
[/TR]
[TR]
[TH]7
[/TH]
[TD="align: right"]30
[/TD]
[TD="align: right"]8
[/TD]
[/TR]
[TR]
[TH]8
[/TH]
[TD="align: right"]40
[/TD]
[TD="align: right"]9
[/TD]
[/TR]
[TR]
[TH]9
[/TH]
[TD="align: right"]50
[/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TH]10
[/TH]
[TD="align: right"]60
[/TD]
[TD="align: right"]20
[/TD]
[/TR]
[TR]
[TH]11
[/TH]
[TD="align: right"]70
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TH]12
[/TH]
[TD="align: right"]80
[/TD]
[TD="align: right"]40
[/TD]
[/TR]
[TR]
[TH]13
[/TH]
[TD="align: right"]90
[/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TH]14
[/TH]
[TD="align: right"]100
[/TD]
[TD="align: right"]55
[/TD]
[/TR]
[TR]
[TH]15
[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]16
[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]17
[/TH]
[TD]------------
[/TD]
[TD]------------
[/TD]
[/TR]
[TR]
[TH]18
[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

And then here's your data worksheet. Each formula looks up the specified reduction based on the value in Column P for the same row and then subtracts that value from the value in Column E for the same row.

Sheet6[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]O[/TH]
[TH]P[/TH]
[TH]Q[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]198[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]93[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]142[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]191[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]170[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]195[/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]------------[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]------------[/TD]
[TD]------------[/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
Q1	=E1-VLOOKUP(P1,TESTINGTABLE,2,TRUE)
Q2	=E2-VLOOKUP(P2,TESTINGTABLE,2,TRUE)
Q3	=E3-VLOOKUP(P3,TESTINGTABLE,2,TRUE)
Q4	=E4-VLOOKUP(P4,TESTINGTABLE,2,TRUE)
Q5	=E5-VLOOKUP(P5,TESTINGTABLE,2,TRUE)
Q6	=E6-VLOOKUP(P6,TESTINGTABLE,2,TRUE)
Q7	=E7-VLOOKUP(P7,TESTINGTABLE,2,TRUE)
Q8	=E8-VLOOKUP(P8,TESTINGTABLE,2,TRUE)
Q9	=E9-VLOOKUP(P9,TESTINGTABLE,2,TRUE)
Q10	=E10-VLOOKUP(P10,TESTINGTABLE,2,TRUE)
Q11	=E11-VLOOKUP(P11,TESTINGTABLE,2,TRUE)
Q12	=E12-VLOOKUP(P12,TESTINGTABLE,2,TRUE)
Q13	=E13-VLOOKUP(P13,TESTINGTABLE,2,TRUE)

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

Name	Refers To
TESTINGTABLE	=Sheet5!$A$1:$B$14

<tbody>
[TD="bgcolor: #FFFFFF"] Workbook Defined Names [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]