Searching anything within a cell and it exists in a column

[apexRaiden]

Good day,

I am trying to formulate a process where I can do a lookup or a search of ANYTHING within a cell and see if it exists in a column of entries.
The cell in question has a few lines of data but specifically has a line of data that could either have 1 entry or multiple entries that are separated by commas. I am trying to find a way to look for THOSE entries are within the column of data/entries to see if they match/exist. My end goal is to then say that if this does exist in column, then populate the rest of this entries details that are on another sheet. Almost as a IF TRUE THEN = 2ndSheet CELL DATA.

I know this formula doesn't work, but I hope it helps in the sense of explanation
=vlookup("*"&B2&"*",A:A,1,FALSE)

Again, I understand that this doesn't work, but I am just trying to help paint a picture of what im trying to obtain.
Is ANYTHING within THIS CELL over here in this column of entries?

Thank you!

 

[Joe4]

Hello.
What would help "paint a picture" even better is if you could show us some sample data and your expected output (as they say, "a picture says a thousand words!").
I think if people get a visual representation of what you are trying to do, and what the data you are working with looks likes, you will increase your odds of getting replies.
Just be sure to "dummy up your data" and remove any sensitive information first.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[apexRaiden]

Hello.
What would help "paint a picture" even better is if you could show us some sample data and your expected output (as they say, "a picture says a thousand words!").
I think if people get a visual representation of what you are trying to do, and what the data you are working with looks likes, you will increase your odds of getting replies.
Just be sure to "dummy up your data" and remove any sensitive information first.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Ah yes, this makes sense. Thank you and apologies!

This would be the best way of me showing the data and what I'm looking to find.
Needing to See if any of the NUMBERS within the B cells, exist in A column. The formula issues I'm running into, or maybe haven't overcome yet, is the "test" or other text that's within my cells are getting in the way of the check.

Because again, my END GOAL is to do an IF THEN result of if the ID is there in Column A, I want the Name's with ID's entry to populate it's information that resides on another sheet.

 

[Joe4]

I am still not quite certain what you want/expect your result from the example you show to look like.
Can you show us a FULL example, i.e. sample data and expected output you want from that data?

 

[apexRaiden]

I am still not quite certain what you want/expect your result from the example you show to look like.
Can you show us a FULL example, i.e. sample data and expected output you want from that data?

Columns E and on are something i can look at and can probably handle later on... But A, B and C is what I'm focusing on at the moment.
I can already look up A against B with a different formula, but what im trying to accomplish relies on me findings B against A because then that will allow me to fulfill an IF/THEN statement which will pull the entries of columns E and on from another sheet.

So in short and maybe writing it out again might help understand what I am trying to accomplish....

I am trying to see if I can do a find/search of ANY TEXT within B2:B6 exists within A:A, and if so - return true.
I already have a formula that goes finding A2 within B:B but that doesnt help with my next step of my goal.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Database ID	Name with ID	Expected Output		Original Name	Name's ID	Name's Attribute 1	Name's Attribute 2	Name's Attribute 3	Name's Attribute 4
2	CVE-2023-1	Test CVE-2023-4	TRUE
3	CVE-2023-2	Test CVE-2023-9	FALSE
4	CVE-2023-3	Test CVE-2023-1, CVE-2023-2, CVE-2023-3, CVE-2023-5	TRUE
5	CVE-2023-4	Test CVE-2023-5	TRUE
6	CVE-2023-5	Test CVE-2023-10	FALSE
Sheet1

 

[Joe4]

So, if you had a value in column B like this:

CVE-2023-4,
CVE-2023-7,
CVE-2023-8

You would want to return "True", as "CVE-2023-4" is found in column A, even though the other 2 aren't?
And, do commas separate the complete values we need to check, as shown in your example (we need to know how to break up the strings we need to check for)?

The only way I can think of doing something like this is with VBA (I do not know of any strictly formulaic solutions to this sort of problem).
Are you open to a VBA solution?

 

[apexRaiden]

So, if you had a value in column B like this:

CVE-2023-4,
CVE-2023-7,
CVE-2023-8

You would want to return "True", as "CVE-2023-4" is found in column A, even though the other 2 aren't?
And, do commas separate the complete values we need to check, as shown in your example (we need to know how to break up the strings we need to check for)?

The only way I can think of doing something like this is with VBA (I do not know of any strictly formulaic solutions to this sort of problem).
Are you open to a VBA solution?

Correct. I would need a "TRUE" if one or multiple in B are matched in A.
Yes, commas do separate the values that I am looking for.

I've never touched VBA before, but I'm not apposed to it or learning how to work with it.

 

[Joe4]

OK, it may take me a little bit to come up with the code, but one more clarification needed.

Is there always a word like "Test" and carriage return before the first value you want to look at in a cell?
It is critically important that we get an accurate depiction of the structure of your data, as the VBA code is going to be dependent upon it appearing a certain way.
So we need the examples to be as realistic as possible. Otherwise, the code we come up with may work on the sample data you posted here, but not you real data!

 

[apexRaiden]

I completely understand.

The data I am looking for is going to come after this specific string, and is the case for every cell within column B

“CVE(s): “

For for an example…..

Name: xxx

ID: xxx

CVE(s): CVE-2023-1111, CVE-2023-2222, CVE-2023-3333.

Hope this helps. Please let me know if more examples are needed. Also, the last subnet of digits is not restricted to only 4 digits, if that makes a difference.

 

[Joe4]

One last question on the data. Are there "soft" carraige returns in the data?
If you are not sure, one would would be to temporarily widen column B to something like "100".
If you do that, does all the data show up on one line, or does it appear on multiple lines within that cell?