Match not Matching (Number with Text)

[Morgan Moore]

Hi All,

I am using a formula to look into a tab ("Calculations") to return a value if two criteria from this tab ("Report") match. This formula works very well except when the matching criteria is a number with text next to it.

For example, I need to match sample number ("K2") 12345 with the sample number on the other tab. If 12345 is present then it returns the proper value. Let's say I run the sample in duplicate or triplicate. Then the sample number becomes 12345A, 12345B, and 12345C respectively. Even if the sample numbers are actually the same, the formula is not matching and returning the value. I tried re-naming as 12345-1, 12345-2, etc, but doesn't seem to recognize that either.

Here is the formula:

=INDEX(Calculations!$X$1:$X$500,MATCH(1,IF(Calculations!$Q$1:$Q$500=--$K$2,IF(Calculations!$B$1:$B$500=$D8,1)),0))

I tried making both tabs "General" format, but it doesn't seem to work. Any advice? I don't really want to do a major overhaul of the formula unless it can be plugged into VBA and loop until it finds the last sample in the list? I'm not even sure where to start with that.

I have the same issue with this LOOKUP formula to find the sample number and return product name:

=IFERROR(VLOOKUP(--K2,SampleInfo!C:E,3,0),"No Product Name")

It does work if i remove the "--" from "--K2" however, it will not work for just numbers (12345), so I'm at a loss here.

All of these formulas are tied to one another. It is a complex workbook that also uses VBA to get the job done faster. I am sorry if I left important information out...please feel free to ask anything if it will help solve this issue.

Thanks for your input!

 

[Mike H..]

Hi All,

I am using a formula to look into a tab ("Calculations") to return a value if two criteria from this tab ("Report") match. This formula works very well except when the matching criteria is a number with text next to it.

For example, I need to match sample number ("K2") 12345 with the sample number on the other tab. If 12345 is present then it returns the proper value. Let's say I run the sample in duplicate or triplicate. Then the sample number becomes 12345A, 12345B, and 12345C respectively. Even if the sample numbers are actually the same, the formula is not matching and returning the value. I tried re-naming as 12345-1, 12345-2, etc, but doesn't seem to recognize that either.

Here is the formula:

=INDEX(Calculations!$X$1:$X$500,MATCH(1,IF(Calculations!$Q$1:$Q$500=--$K$2,IF(Calculations!$B$1:$B$500=$D8,1)),0))

I tried making both tabs "General" format, but it doesn't seem to work. Any advice? I don't really want to do a major overhaul of the formula unless it can be plugged into VBA and loop until it finds the last sample in the list? I'm not even sure where to start with that.

I have the same issue with this LOOKUP formula to find the sample number and return product name:

=IFERROR(VLOOKUP(--K2,SampleInfo!C:E,3,0),"No Product Name")

It does work if i remove the "--" from "--K2" however, it will not work for just numbers (12345), so I'm at a loss here.

All of these formulas are tied to one another. It is a complex workbook that also uses VBA to get the job done faster. I am sorry if I left important information out...please feel free to ask anything if it will help solve this issue.

Thanks for your input!

Hi,

I think I understand the issue, you're trying to match 12345 with 12345A and it's not working. Try this which is still an array formula. I don't know if k2 is text or a number so I left the -- in because it does no harm

=INDEX(Calculations!$X$1:$X$500,MATCH(1,IF(--LEFT(Calculations!$Q$1:$Q$500,LEN($K$2))=--$K$2,IF(Calculations!$B$1:$B$500=$D8,1)),0))

It sounds like you have a mix of text that look like numbers and if you could upload a copy of the workbook then someone will sort it out.

 

[jeffreyweir]

You can use WildCards within a match, to get it to do a partial match. Google "Match" & "WildCards", or upload a sample file and I'll take a look.

 

[NARAYANK991]

Hi Morgan ,

Can you try the following array formula , entered using CTRL SHIFT ENTER ?

=INDEX(Calculations!$X$1:$X$50,MATCH(1,(ISNUMBER(SEARCH($K$2&"*",Calculations!$Q$1:$Q$50)))*COUNTIF($D8,Calculations!$B$1:$B$50),0))

Narayan

 

[Morgan Moore]

Hi,

I think I understand the issue, you're trying to match 12345 with 12345A and it's not working. Try this which is still an array formula. I don't know if k2 is text or a number so I left the -- in because it does no harm

=INDEX(Calculations!$X$1:$X$500,MATCH(1,IF(--LEFT(Calculations!$Q$1:$Q$500,LEN($K$2))=--$K$2,IF(Calculations!$B$1:$B$500=$D8,1)),0))

It sounds like you have a mix of text that look like numbers and if you could upload a copy of the workbook then someone will sort it out.

Thanks Mike,

I'm actually trying to match 12345 with 12345, and then 12345D with 12345D. Thanks for your help, but this doesn't seem to fix the error.

I think you are right though...a mix of text that looks like numbers, etc. I will try to upload a copy of the workbook, but legally may not be able to. :/

Still working on it

 

[Morgan Moore]

Hi Morgan ,

Can you try the following array formula , entered using CTRL SHIFT ENTER ?

=INDEX(Calculations!$X$1:$X$50,MATCH(1,(ISNUMBER(SEARCH($K$2&"*",Calculations!$Q$1:$Q$50)))*COUNTIF($D8,Calculations!$B$1:$B$50),0))

Narayan

Narayan, this worked beautifully! Thank you all so much! Glad to be a part of this site.