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

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
119
Office Version
  1. 2007
Platform
  1. Windows
Dear Sir,

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

Code:
=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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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