Number Search in Formula return wrongs information.

[helpme20]

In the formula I am using below:


=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(b1,INDIRECT("'2011a.xlsx'!"&codes)))),LOOKUP(9.99999999999999E+307,SEARCH(b1,INDIRECT("'2011a.xlsx'!"&codes)),INDIRECT("'2011a.xlsx'!"&prodname)),"")


In column B1, my number I am searching for is between a 4 and 6 digit number.

If I put in 5229, it will still return the information associated with Code 522922.

Is there something I can add to my formula to correct this?

 

[helpme20]

That works. Thank you very much!

 

[Peter_SSs]

=IFERROR(LOOKUP(1E100,SEARCH(B15&",",B2:B13&","),C2:C13),"")

Biff/helpme20
I think that still has the same sort of issue referred to here ..

If I put in 5229, it will still return the information associated with Code 522922.

.. see my changed data in B11 below and your formula result in cell B16. I think both the prefix & suffix to the number need to be checked/enforced per the formula amendment in B17.

Excel Workbook
	B	C
2	564223	Potassium Hydroxide 50%
3	694885	Potassium Hydroxide AR Pellets
4	390311	Potassium Lactate
5	569375, 345976, 98765, 132060, 421414, 548190	Potassium MetaBisulfite
6	5229	Potassium Nitrate
7	790556	Potassium Nitrite
8	856094	Potassium Permanganate
9	503659, 565346, 480890, 522922	Potassium Persulfate
10	640919	Potassium Phosphate Monobasic (Kilo)
11	67679, 849244, 65229, 742602	Potassium Phosphate Tripolyphosphate
12	948555	Potassium Sorbate
13	192122	Potassium Stannate
14
15	5229
16	Potassium Phosphate Tripolyphosphate
17	Potassium Nitrate
Lookup

 

[T. Valko]

Biff/helpme20
I think that still has the same sort of issue referred to here .... see my changed data in B11 below and your formula result in cell B16. I think both the prefix & suffix to the number need to be checked/enforced per the formula amendment in B17.

Excel Workbook
	B	C
2	564223	Potassium Hydroxide 50%
3	694885	Potassium Hydroxide AR Pellets
4	390311	Potassium Lactate
5	569375, 345976, 98765, 132060, 421414, 548190	Potassium MetaBisulfite
6	5229	Potassium Nitrate
7	790556	Potassium Nitrite
8	856094	Potassium Permanganate
9	503659, 565346, 480890, 522922	Potassium Persulfate
10	640919	Potassium Phosphate Monobasic (Kilo)
11	67679, 849244, 65229, 742602	Potassium Phosphate Tripolyphosphate
12	948555	Potassium Sorbate
13	192122	Potassium Stannate
14
15	5229
16	Potassium Phosphate Tripolyphosphate
17	Potassium Nitrate
Lookup

Yeah, that'll make it more robust.

 

[helpme20]

Biff/helpme20
I think that still has the same sort of issue referred to here .... see my changed data in B11 below and your formula result in cell B16. I think both the prefix & suffix to the number need to be checked/enforced per the formula amendment in B17.

Excel Workbook
	B	C
2	564223	Potassium Hydroxide 50%
3	694885	Potassium Hydroxide AR Pellets
4	390311	Potassium Lactate
5	569375, 345976, 98765, 132060, 421414, 548190	Potassium MetaBisulfite
6	5229	Potassium Nitrate
7	790556	Potassium Nitrite
8	856094	Potassium Permanganate
9	503659, 565346, 480890, 522922	Potassium Persulfate
10	640919	Potassium Phosphate Monobasic (Kilo)
11	67679, 849244, 65229, 742602	Potassium Phosphate Tripolyphosphate
12	948555	Potassium Sorbate
13	192122	Potassium Stannate
14
15	5229
16	Potassium Phosphate Tripolyphosphate
17	Potassium Nitrate
Lookup

This code above in B17 works perfectly. When someone using Excel 2003 opens the .xlsx version, it trys to convert it and has alot of errors.

What would be the code to write the above formula in 2003 code?

 

[T. Valko]

This code above in B17 works perfectly. When someone using Excel 2003 opens the .xlsx version, it trys to convert it and has alot of errors.

What would be the code to write the above formula in 2003 code?

Try this one will work in any version of Excel:

=LOOKUP("zzzzz",CHOOSE({1,2},"",LOOKUP(1E100,SEARCH(B15&",",B2:B13&","),C2:C13)))

 

[Aladin Akyurek]

In the formula I am using below:


=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(b1,INDIRECT("'2011a.xlsx'!"&codes)))),LOOKUP(9.99999999999999E+307,SEARCH(b1,INDIRECT("'2011a.xlsx'!"&codes)),INDIRECT("'2011a.xlsx'!"&prodname)),"")


In column B1, my number I am searching for is between a 4 and 6 digit number.

If I put in 5229, it will still return the information associated with Code 522922.

Is there something I can add to my formula to correct this?

Define BigNum as referring to (using Insert | Name | Define or Formulas | Name Manager)...

=9.99999999999999E+307

Let B1 house a search string like 5229, E1 codes, and F1 prodname.

Note that codes and prodname must not be defined as dybamic named ranges.

If you are on Excel 2007 or later, try...

=IFERROR(LOOKUP(BigNum,SEARCH(" "&B1&","," "&INDIRECT("'2011.xlsx'!"&$E$1)&","),INDIRECT("'2011.xlsx'!"&$F$1)),"")

On all (other) systems...

First define BigStr as referring to...

=REPT("z",255)

and now invoke:

=LOOKUP(BigStr,CHOOSE({1,2},"",LOOKUP(BigNum,SEARCH(" "&B1&","," "&INDIRECT("'2011.xlsx'!"&$E$1)&","),INDIRECT("'2011.xlsx'!"&$F$1))))