Vlookup range - Complex

okiedokie2017

New Member
Joined
Sep 14, 2017
Messages
14
Hello,

Normally if we have a table such as

<= 5 A
>5 B
>10 C

So if I have a number 6, it will give me B.
We all can use Vlookup for this range thing.

However, if I have an IF formula like below, I can not translate it into a Vlookup one:

=IF(A1>10, (A1-5)/200, IF(A1> 20, (A1-20)/400, IF(A1>30, (A1-30)/500, A1-30)))

How can I possible create a table so Ican do Vlookup because of the "A1" thing? As I need to apply this to the whole column of A, not just A1.

Please help?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hello,

You could have :
In cell D1 : 0
In cell D2 : 6
In cell D3 :10
then in column E
In cell E1 : A
In cell E2 : B
In cell E3 : C

With your input value in cell A1,

you can have in cell B1 the following formula :

Code:
=INDEX($E$1:$E$3,MATCH(A1,$D$1:$D$3,1))

Hope this will help
 
Upvote 0
Hello,

You could have :
In cell D1 : 0
In cell D2 : 6
In cell D3 :10
then in column E
In cell E1 : A
In cell E2 : B
In cell E3 : C

With your input value in cell A1,

you can have in cell B1 the following formula :

Code:
=INDEX($E$1:$E$3,MATCH(A1,$D$1:$D$3,1))

Hope this will help



Hi, Thanks for your reply but this is not what Iam looking for.
I have this formula which I am trying to simplify it:

=IF(A1>10, (A1-5)/200, IF(A1> 20, (A1-20)/400, IF(A1>30, (A1-30)/500, A1-30)))

This formula applies to the whole column A meaning A1 will be A2, A3, A4...... in row 2, 3,4, 5....

Normally, I would be able to do a Vlookup range if there are no formulas like (A1-5)/200.
How can I still create a table range so that I can do Vlookup range???
 
Upvote 0
Simplifying IF Formula

Hello,

I have this formula which I am trying to simplify it:

=IF(A1>30, (A1-5)/200, IF(A1> 20, (A1-20)/400, IF(A1>10, (A1-30)/500, A1-30)))

This formula applies to the whole column of A.
Is there a way I can simplify it?

Normally if we have:
=IF(A1>30, 5, IF(A1> 20, 7, IF(A1>10, 8, 9)))
It would be easy to simplify it by creating a table range then do a Vlookup range such as:

0 9
11 8
21 7
31 5

How can I do the same with the first formula?
 
Upvote 0
Re: Simplifying IF Formula

I don't see a good way to simplify it, since the amount you "jump" by in each section isn't consistent.
If the formula does what you want, why not just use it "as-is"?
Sometimes complex things can only be simplified so far, by their very nature.
 
Upvote 0
Re: Simplifying IF Formula

IMHO, your nested IF expression is just fine. I think it is sufficiently efficient, and it is easy to read and maintain.

A lookup table might look something like the following.

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
G
[/th][th]
H
[/th][th]
I
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]
-1.00E+300​
[/td][td]
30​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]
10.0000000000001​
[/td][td]
30​
[/td][td]
500​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]
20.0000000000001​
[/td][td]
20​
[/td][td]
400​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]
30.0000000000001​
[/td][td]
5​
[/td][td]
200​
[/td][/tr]
[/table]

Then the formula would be:

=(A1-VLOOKUP(A1,$G$1:$I$4,2)) / VLOOKUP(A1,$G$1:$I$4,3)

The value in G1 (-1E+300) is intended to represent the smallest "possible" number that you might encounter. -1E+300 is not the smallest value that we can enter in Excel; and it is not the smallest value that we can calculate in Excel. But it is probably small enough for practical purposes; perrhaps even smaller than you require. Change it as you wish.

Normally, I do not like to depend on constants with 15 significant digits. 10.0000000000001 is not the smallest value for which A1>10 should be true [1]. But in fact, the breakpoints in H2:H4 are consistent with the way that A1>10, A1>20 and A1>30 work in Excel.

Arguably, one "lookup" (your IF expression) should be better than two (the VLOOKUP implementation). But that might not be true here, since the VLOOKUP implementation might be faster than the several comparisons in the IF expression.


-----
[1] For example, 10+1E-14 is greater than 10. But in fact, 10+1E-14>10 returns FALSE even though 10+1E-14-10>0 returns TRUE. The reason is: for comparison operators (only), Excel rounds the left and right operands to 15 significant digits for the purpose of the comparison.
 
Last edited:
Upvote 0
Re: Simplifying IF Formula

I have merged the two threads together.

As Jon said, please do not start a new thread on it since it is a direct continuation of your previous thread. Simply post back to the original. We do not want multiple threads on the same question.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top