Multiple Comma Seperated Entry LookUp

If I understood the question correctly, here is a formula you can use. It will only work in excel 2007.

Sorry, it is in the attached file.

The formula in this file will work in all versions of Excel

Thanks andreyman3d2k,
I believe you are close, but it appears the search only accurs in that same line.   I need for the search to accur in the whole column?

Thanks
Spanoird

If you want to search many cells in column B for a value in column A, you might try a formula like:
=IF(COUNTIF(B$2:B$1000,"*" & A4 & "*"),"Exists","")

If you want to determine if any of the values in comma separated cell B2 are found in column A, then you might try a formula like:
=IF(SUM(COUNTIF(B2,"*" & A$2:A$1000 & "*")),"Exists","")

This formula is the opposite of the previous formula, which searched many cells in column B for a given value in column A.

Brad

Hi Byundt,
thanks for your participation and I opologize for the delayed response back, I have attached a file that makes use of your second offered solution.   From what I can see it seems to not capture the desired results.    Could you review and let me know if I am missing someting.

I am require to perform qc validations on comma sperated data entered on column B to ensure it is found in column A.  

Thanks
Spanoird

Disregard the previouse spreadsheet example attached, I realized it was an incomplete data set.   Please see update attached to this posting.

Thanks

Points increased update.

Anybody there?   Did I upset someone?

Not sure of the solution to the problem (hence I stopped commenting : ) but I do know a reason why this won't work, at least the way it is now:

=IF(SUM(COUNTIF(B2,"*" & A$2:A$1000 & "*")),"Exists","")

this is taking B2 as having the value you are looking for, however it can have multiple value in the one cell...

if there were no comma spearated values in the B column, this would work:

=IF(ISERROR(VLOOKUP(B2,$A$2:$A$3447,1,FALSE)),"Error","")

Thanks for the comments andrewman3d2k,

unfortunely columnB will always have multi comma delimited entries that will need to be validated through columnA.

Carlos

Thanks AndrewMan3d2k!
The formula look to be the best promising so far, I believe with a bit of tweaking we can get it thier.  Smart move to take it to text format.

I checked the results of your new formula and found that 4 out of the 10 reported as not found "No" in the B columns were found when searched manually.

Thanks for sticking with me a bit longer.   I am also hard at work in seeking a solution.  Thanks a bunch man!

Carlos

706211345	TRUE
2600290	TRUE
177386	TRUE
11918	not true
11918	not true
E-1-1010	not true
E-1-1010	not true
194672	TRUE
194672	TRUE
3400074	TRUE
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

On my spreadsheet the number 11918 does not exist in column A

there is:

0000111918

You are right, this only leaves the text base entries to contend with (E-1-1010, E-1-1080, ext.)

Guys,
I apologize; I have been swapped with other work and have not had a chance to test these solutions out.    Give me until tomorrow to provide feed abck.    Do appreciate your assistance in seeking a solution for me though!

Both methods look promising, more to come...

Carlos

andreyman3d2k, your assistance with this problem produce a formula that did capture some of the entry errors, so I have awarded points to you.   Do want to thank you for sticking it out!  Your formula appears to be usable in other areas as well.   Thank You!

Brad, your formula hit the mark I was able to identify key entry errors building my confidence in my data and business use.   Your VBA code was the ticket!  I always wanted to 'dabble' in VBA and with this need and your code I have learned to make use of VBA, Thanks a million!

Great work Guys!   Till next time, have a good day!
Carlos

Glad to be of some help. Thanks for the points & interesting question.

Brad,
what formula would be needed for the reverse qc check?  To check if single entry in columnA exist in a comma seperated multy entry in colum B.  For example:
ColumnA      ColumnB
23423           12232, 314454, 42435
235565          25566, ,235432, 3234, 235666
2555              5345, 54534, 346777, 345345, 353454

Let me know if this warrants posting a new question all together or similar and would require a quick tweak.

Thank You!
Carlos

Carlos,
The reverse QC check is covered by the formula in http:/#25593818

To search many cells in column B for a value in column A, you might try a formula like:
=IF(SUM(COUNTIF(B$2:B$4000,{A2,"*" & A2 & "*"})),"Exists","Not found")

This formula ignores any leading zeros in cell A2. It converts the value in cell A2 into text, and so doesn't care whether A2 is a number or text that looks like a number. The bit with the curly braces and SUM is to trap single values entered as numbers in column B.

Brad

Brad,
in using the formula =IF(SUM(COUNTIF(B$2:B$4000,{A2,"*" & A2 & "*"})),"Exists","Not found") I get the following error message "The formula you typed contains an error".   I tried several substitutions and variations to hope it would work?

Carlos

Carlos,
I tested the formula using real values, then substituted in the cell references--forgetting that cell references can't appear inside the array constant. The following formula works in Excel 2003 exactly as posted.

=IF(COUNTIF(B$2:B$4000,A2)+COUNTIF(B$2:B$4000,"*" & A2 & "*"),"Exists","Not found")

Sorry about the inconvenience.

Brad

Brad,
not a problem, on the contrary.   The change did the trick, great formula!

I want to award you points, is there still an opportunity when closed, or should I create a new ticket.

Thanks a Million!
Carlos