IF(ISNUMBER(SEARCH formula considering 2.5 and 5 as same. How to check for exact match?

[PritishS]

Dear Sir,

I have a formula based on which one excel cell changes value based on other cell value. The formula I am using

=IF(ISNUMBER(SEARCH("2.5",I38)),"0.02564",IF(ISNUMBER(SEARCH("3",I38)),"1.03093",IF(ISNUMBER(SEARCH("5",I38)),"0.05263",IF(ISNUMBER(SEARCH("7.5",I38)),"0.08108",IF(ISNUMBER(SEARCH("10",I38)),"0.11111",IF(ISNUMBER(SEARCH("12.5",I38)),"0.14286",IF(ISNUMBER(SEARCH("15",I38)),"0.176470",IF(ISNUMBER(SEARCH("20",I38)),"0.25",""))))))))

If I enter a value in I38 cell in J38 its gives value.

Problem is, I am not getting the which I set for 7.5, 12.5, 15.

For example:
If I38= 2.5 then J38=0.02564
If I38= 3 then J38=1.03093
If I38= 5 then J38=0.05263

Now If I38= 7.5 should return in J38=0.08108
Instead of that it is showing J38= 0.05263 ( which is value for '5')

Similarly for I38= 12.5 then J38= 0.02564 ( which is value for '2.5')
for I38= 15 then J38=0.05263 ( which is value for '5')

I assume it is happening because SEARCH function finding a partial match.

Is there any way I can check for exact match, so my formula will return value according to the selection?

Thanks and regards,
PritiS

 

[tyija1995]

Hi,

The IF function will return the solution when it meets the first criteria, so in your example of I38 = 7.5 you want to see 0.08108 but 0.05263 is showing because 7.5 contains a 5 which you are searching for BEFORE 7.5, hence the result from 5 is being picked up.

There is a function called EXACT that you could look at, in this instance I would rearrange the search formulae so that you don't "overlap" - so search 7.5 before 5, and search 12.5 before 2.5

 

[steve the fish]

You wouldnt normally do a lookup type of formula as you have tried there. Id be creating a lookup table. This is how it would look hardcoded into the formula:

=VLOOKUP(I38,{2.5,0.02564;3,1.03093;5,0.05263;7.5,0.08108;10,0.11111;12.5,0.14286;15,0.17647;20,0.25},2,0)

 

[PritishS]

Hi

tyija1995 and steve the fish , Thanks to both you for your valuable time. Steve's VLOOKUP formula works as expected!! Everyday I learn something new in Excel.

Once again thank you very much to both of you. Have a great day!!

Thanks & regards,
PritiS

 

[Gerald Higgins]

And here's another way to get the same results.

NOTE - you say the result for 3 should be 1.03093.
I'm guessing you've made a mistake here, and the result should really by 0.03093.

If I'm right, then this formula gives you the same results.
This solution depends not on a lookup, but on understanding the mathematical relationship between the values.
This might be a better approach, as it will cope with values that have not been encoded in the lookup table, or in the lookup formula.

=I38/(100-I38)

 

[PritishS]

Hi Gerald Higgins

I am very much agree with your opinion.
Actually these values are pre-defined values. There is no math involved. Thus for now VLOOKUP formula works for me.
Thanks your replying me. Have a great day!

Thanks & Regards,
PritiS