Number Search in Formula return wrongs information.

helpme20

Board Regular
Joined
Aug 28, 2010
Messages
102
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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
For simplicity's sake, let's take out the IF(ISNUMBER check, and the INDIRECT..

Assuming codes is C1:C10 and prodname is D1:D10
That leaves
=LOOKUP(9.99999999999999E+307,SEARCH(B1,C1:C10),D1:D10)

Please enter the correct ranges
And describe what type of data is in those ranges, and what you intend the formula to do.
 
Upvote 0
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?
How about posting some sample data.

A couple of things that will shorten the formula:
  • Replace ISNUMBER with COUNT
  • Replace 9.99999999999999E+307 with 1E100
 
Upvote 0
How about posting some sample data.



A couple of things that will shorten the formula:
  • Replace ISNUMBER with COUNT
  • Replace 9.99999999999999E+307 with 1E100

in a separate workbook '2011a.xlsx'!"&codes,

codes in the named range ($B:$B)

(this is just an example of the first 12 row in Column B)

<TABLE style="WIDTH: 142pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=189><COLGROUP><COL style="WIDTH: 142pt; mso-width-source: userset; mso-width-alt: 6912" width=189><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 142pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=189>564223</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 142pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=189>694885</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 142pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=189>390311</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 142pt; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=40 width=189>569375, 345976, 98765, 132060, 421414, 548190</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 142pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=189>5229</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 142pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=189>790556</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 142pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=189>856094</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 142pt; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=40 width=189>503659, 565346, 480890, 522922</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 142pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=189>640919</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 142pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=189>67679, 849244, 742602</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 142pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=189>948555</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 142pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=189>192122</TD></TR></TBODY></TABLE>

prodname is also a named range in a separate workbook:

(here is a screenshot of the first 12 rows that go along with the product codes)

<TABLE style="WIDTH: 235pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=313><COLGROUP><COL style="WIDTH: 235pt; mso-width-source: userset; mso-width-alt: 11446" width=313><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=313>Potassium Hydroxide 50%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=313>Potassium Hydroxide AR Pellets</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=313>Potassium Lactate</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=40 width=313>Potassium MetaBisulfite</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=40 width=313>Potassium Nitrate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=313>Potassium Nitrite</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=313>Potassium Permanganate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=313>Potassium Persulfate </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=313>Potassium Phosphate Monobasic (Kilo)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=313>Potassium Phosphate Tripolyphosphate</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=40 width=313>Potassium Sorbate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 235pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=313>Potassium Stannate</TD></TR></TBODY></TABLE>



In the workbook that contains the formula"

B1 is where I enter the code I want to lookup.


If you notice, in row 5 of the codes it lists 5229, but row 8 also has a code that is 522922.

When you put in 5229, it returns Potassium Persulfate instead of Potassium Nitrate.



Does that explanation help?
 
Upvote 0
Here's one way.

Make your named ranges NOT be entire columns like B:B, use B1:B1000 or something.
Unless in XL2007 or higher, even then I don't recommend using entire column refs.

Enter the formula with CTRL + SHIFT + ENTER

I have removed the IF(ISNUMBER and INDIRECT parts
You should be able to apply that.
You only need to test the MATCH for ISNUMBER.

=INDEX(prodname,MATCH(1,SEARCH(E1,codes),0))
 
Upvote 0
in a separate workbook '2011a.xlsx'!"&codes,

codes in the named range ($B:$B)

(this is just an example of the first 12 row in Column B)

<TABLE style="WIDTH: 142pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=189 border=0><COLGROUP><COL style="WIDTH: 142pt; mso-width-source: userset; mso-width-alt: 6912" width=189><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=189 height=20>564223</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=189 height=20>694885</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=189 height=20>390311</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: white" width=189 height=40>569375, 345976, 98765, 132060, 421414, 548190</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=189 height=20>5229</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=189 height=20>790556</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=189 height=20>856094</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=189 height=40>503659, 565346, 480890, 522922</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=189 height=20>640919</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=189 height=20>67679, 849244, 742602</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=189 height=20>948555</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 142pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=189 height=20>192122</TD></TR></TBODY></TABLE>

prodname is also a named range in a separate workbook:

(here is a screenshot of the first 12 rows that go along with the product codes)

<TABLE style="WIDTH: 235pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=313 border=0><COLGROUP><COL style="WIDTH: 235pt; mso-width-source: userset; mso-width-alt: 11446" width=313><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=313 height=20>Potassium Hydroxide 50%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=313 height=20>Potassium Hydroxide AR Pellets</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=313 height=20>Potassium Lactate</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=313 height=40>Potassium MetaBisulfite</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=313 height=40>Potassium Nitrate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=313 height=20>Potassium Nitrite</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=313 height=20>Potassium Permanganate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=313 height=20>Potassium Persulfate </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=313 height=20>Potassium Phosphate Monobasic (Kilo)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=313 height=20>Potassium Phosphate Tripolyphosphate</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=313 height=40>Potassium Sorbate</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 235pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=313 height=20>Potassium Stannate</TD></TR></TBODY></TABLE>



In the workbook that contains the formula"

B1 is where I enter the code I want to lookup.


If you notice, in row 5 of the codes it lists 5229, but row 8 also has a code that is 522922.

When you put in 5229, it returns Potassium Persulfate instead of Potassium Nitrate.



Does that explanation help?
Not tested...

Try this...

=IF(COUNT(LOOKUP(1E100,SEARCH(B1&",",INDIRECT("'2011a.xlsx'!"&codes&",")))),LOOKUP(1E100,SEARCH(B1&",",INDIRECT("'2011a.xlsx'!"&codes&",")),INDIRECT("'2011a.xlsx'!"&prodname)),"")
 
Upvote 0
Not tested...

Try this...

=IF(COUNT(LOOKUP(1E100,SEARCH(B1&",",INDIRECT("'2011a.xlsx'!"&codes&",")))),LOOKUP(1E100,SEARCH(B1&",",INDIRECT("'2011a.xlsx'!"&codes&",")),INDIRECT("'2011a.xlsx'!"&prodname)),"")
If you're using Excel 2007 or later...

=IFERROR(LOOKUP(1E100,SEARCH(B1&",",INDIRECT("'2011a.xlsx'!"&codes&",")),INDIRECT("'2011a.xlsx'!"&prodname)),"")
 
Upvote 0
If you're using Excel 2007 or later...

=IFERROR(LOOKUP(1E100,SEARCH(B1&",",INDIRECT("'2011a.xlsx'!"&codes&",")),INDIRECT("'2011a.xlsx'!"&prodname)),"")

I am using Excel 2007

I tried this formula and it did not return any value.

I then put in the word "Test" inside the double quotes and it returned the word Test.

I am up for more suggestions.
 
Upvote 0
I am using Excel 2007

I tried this formula and it did not return any value.

I then put in the word "Test" inside the double quotes and it returned the word Test.

I am up for more suggestions.
It works just fine for me when I test it with the data in the same file.

Why are you using INDIRECT?
 
Upvote 0
It works just fine for me when I test it with the data in the same file.

Why are you using INDIRECT?
Book1
BC
2564223Potassium Hydroxide 50%
3694885Potassium Hydroxide AR Pellets
4390311Potassium Lactate
5569375, 345976, 98765, 132060, 421414, 548190Potassium MetaBisulfite
65229Potassium Nitrate
7790556Potassium Nitrite
8856094Potassium Permanganate
9503659, 565346, 480890, 522922Potassium Persulfate
10640919Potassium Phosphate Monobasic (Kilo)
1167679, 849244, 742602Potassium Phosphate Tripolyphosphate
12948555Potassium Sorbate
13192122Potassium Stannate
Sheet1

Book1
B
155229
16Potassium Nitrate
Sheet1

Formula entered in B16:

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

Forum statistics

Threads
1,224,520
Messages
6,179,266
Members
452,902
Latest member
Knuddeluff

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