multiple nesting formula to eliminate the False entry

[scotorsic]

I have a formula to place a value in a cell based on the data entered in another cell. I would like to cells where not evaluation data has been entered to remain blank and not return the "False" the formula is

=IF(ISNUMBER(SEARCH("2",E2)),0.02,IF(ISNUMBER(SEARCH("8",E2)),0.08,IF(ISNUMBER(SEARCH("1",E2)),0.1,IF(ISNUMBER(SEARCH("3",E2)),0.2))))

I have tried to insert the double quotation marks ("") in various places but have not found the key.

 

[philwojo]

Try this:

=IF(ISBLANK(E2),"",IF(ISNUMBER(SEARCH("2",E2)),0.02,IF(ISNUMBER(SEARCH("8",E2)),0.08,IF(ISNUMBER(SEARCH("1",E2)),0.1,IF(ISNUMBER(SEARCH("3",E2)),0.2)))))

 

[Eric W]

Welcome to the forum.

=IF(ISNUMBER(SEARCH("2",E2)),0.02,IF(ISNUMBER(SEARCH("8",E2)),0.08,IF(ISNUMBER(SEARCH("1",E2)),0.1,IF(ISNUMBER(SEARCH("3",E2)),0.2,""))))

or

=IFERROR(LOOKUP(2^14,FIND({3,1,8,2},E2),{0.2,0.1,0.08,0.02}),"")

Notice that the search order is reversed. In case of multiple matches, this is required to preserve the order of your conditions.

 

[gmittar]

nvm, answered better above.

 

[scotorsic]

Thanks! Amazingly quick response!

 

[scotorsic]

I have to study this some more. The first response solved the issue, but this looks much more straight forward. What does the 2^14 represent? Can you convert this into plain text logic?
I do appreciate the fast response!

 

[Eric W]

Here's how the LOOKUP works. Let's say you have ABC8 in E2.

=IFERROR(LOOKUP(2^14,FIND({3,1,8,2},E2),{0.2,0.1,0.08,0.02}),"")

The FIND looks for all 4 values in the array constant {3,1,8,2} and returns an array with the results: {#VALUE ,#VALUE ,4,#VALUE }.

The LOOKUP now looks for the lookup value (2^14) in that array, ignoring error values. If the value is not found, it locates the rightmost value. The maximum length of a cell is 32,767 characters, so that's the largest value FIND can return, so if you choose a number larger than that, it will force LOOKUP to pick the rightmost value. (2^14 =16,384, I should have used 2^15 ).
Then LOOKUP takes the value from the second array in the same position as the value it located.

Hope this explains things!