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,))))))))))))))))))))))))))))))))))

 

[RBusiness]

Hello and thank you. But it doesn't appear to do anything. I know it must be me.

One minor thing that I adjusted was my reference to column P should have been L but I changed it.

So then in this section (below) do I keep the space in row 2? And do I keep the column headings?

Sheet5[TABLE="class: cms_table_html-maker-worksheet"]
<tbody>[TR]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[/TR]
[TR]
[TH="align: center"]1[/TH]
[TD="align: center"]Low End[/TD]
[TD="align: center"]Reduce[/TD]
[/TR]
[TR]
[TH="align: center"]2[/TH]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH="align: center"]3[/TH]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


Do I do anything with this?

Workbook Defined Names[TABLE="class: cms_table_html-maker-worksheet"]
<tbody>[TR]
[TH="align: center"]Name[/TH]
[TH="align: center"]Refers To[/TH]
[/TR]
[TR]
[TH="align: center"]TESTINGTABLE[/TH]
[TD]=Sheet5!$A$1:$B$14[/TD]
[/TR]
</tbody>[/TABLE]

 

[RBusiness]

Note: I'm using Excel 2003 and getting a #NAME? error in column Q

 

[Jeff Meyers]

So then in this section (below) do I keep the space in row 2? And do I keep the column headings?

Sheet5[TABLE="class: cms_table_html-maker-worksheet"]
<tbody>[TR]
[TH="align: center"][/TH]
[TH="align: center"]A
[/TH]
[TH="align: center"]B
[/TH]
[/TR]
[TR]
[TH="align: center"]1
[/TH]
[TD="align: center"]Low End
[/TD]
[TD="align: center"]Reduce
[/TD]
[/TR]
[TR]
[TH="align: center"]2
[/TH]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH="align: center"]3
[/TH]
[TD="align: right"]10
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

There should not be a space in row 2 - both A2 (required) and B2 (not required) should have 0 (zero) values.
No, you do not need the column headings.

Do I do anything with this?

Workbook Defined Names[TABLE="class: cms_table_html-maker-worksheet"]
<tbody>[TR]
[TH="align: center"]Name
[/TH]
[TH="align: center"]Refers To
[/TH]
[/TR]
[TR]
[TH="align: center"]TESTINGTABLE
[/TH]
[TD]=Sheet5!$A$1:$B$14
[/TD]
[/TR]
</tbody>[/TABLE]

This just shows the worksheet and cell range of my named range TESTINGTABLE - your sheet / range will be different. It will not appear on your worksheets, but you will use it in the VLOOKUP forumla.

Hope that helps. Good luck.

 

[RBusiness]

Thank you very much. But for some reason this "=E2-VLOOKUP(L2,

TESTINGTABLE​

,2,TRUE)" is not working. The 'testingtable' sheet is how you said. Might it be excel 2003 needed a different way to reference the other worksheet? It's in the same workbook.

 

[RBusiness]

Thanks Jeff and Everyone,

This is almost solved;

I added an exclamation to the end of the worksheet name, then added absolute "$" characters to the table references so that when I copied the VLOOKUP down it no longer ascended the portion of the formula which references the other worksheet. The sample of my edits are below.

There's only one things left that I didn't think of before. When a figure in "L2" is below "0" the VLOOKUP returns a #NA. Rightly so! However, I wonder if it's possible to have the field default to the contents of the contents of the same row in column E by default?


Current Working Formula

=E2-VLOOKUP(L2,TESTINGTABLE!$A$3:$B$36,2,TRUE)

 

[Jeff Meyers]

There's only one things left that I didn't think of before. When a figure in "L2" is below "0" the VLOOKUP returns a #NA. Rightly so! However, I wonder if it's possible to have the field default to the contents of the contents of the same row in column E by default?

Good to hear that you are getting close. You are correct with the exclamation mark to denote a worksheet name and the dollar signs to make the reference absolute. I had used the TESTINGTABLE as a named range - that was the difference and why it did not require the ! or $ signs.

As far as error trapping for possible #N/A's you have a couple of options. The first is to test for the #N/A error (or others) with ISNA and an IF statement. The second option (and easier option) is to insert a row in your VLOOKUP table (before the 0 - i.e. row 2) and insert a large negative value (i.e. -1,000,000) as the lookup value (Col A) and then have a 0 returned so you would end up with E# - 0 as your end result.

 

[RBusiness]

"Insert a row in your VLOOKUP table (before the 0 - i.e. row 2) and insert a large negative value (i.e. -1,000,000) as the lookup value (Col A) and then have a 0 returned so you would end up with E# - 0 as your end result."

Worked like a charm!

Thank you very much! Your help is not only appreciated but applauded!

RB

 

[Jeff Meyers]

That's great to hear that it worked out for you. Thank you for the very kind feedback.