Vlookup range - Complex

[okiedokie2017]

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?

 

[James006]

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 :

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

Hope this will help

 

[okiedokie2017]

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 :

=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???

 

[okiedokie2017]

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?

 

[Joe4]

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.

 

[joeu2004]

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.

 

[Jon von der Heyden]

Re: Simplifying IF Formula

Looks like a duplicate to me. Questions should be kept to a single thread, with all follow-ups posted back to that thread.

https://www.mrexcel.com/forum/excel-questions/1080284-vlookup-range-complex.html

 

[Joe4]

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.