Searching a single value from multiple values in one cell in a range (or vice versa)

**Flash_82** · 04-15-2014, 04:24 AM

Hi everyone,

I think I'm overlooking something, but I'm stuck with this search/lookup/match thing I'm trying to do.

We have a sheet we send out to our clients, in this sheet they can list up several values in a cell (all in the same format an L, followed by a series of three numbers), these codes are linked to the article they added. Now I want to check if one of these values are on one of the lists we have.

e.g. cell B5 contains "L111, L250, L231" and I want to check if cell B5 contains one of 40 Lxxx combinations. These 40 combinations can be set up as one value per row, making it a range or thrown in one cell. We can not make our clients make a seperate rown or column for each L-combination.

I tried the search function, but it won't search the range of 40 combinations. I'm thinking about matrices perhaps, but I know too little about it.

Any thoughts?

**Richard Buttrey** · 04-15-2014, 04:49 AM

Hi,

If the mountain won't come to Mohammed etc......... then maybe you need a macro that will copy B5 to the first column on the row which has no values to the right and then do a Text to Columns on it. Then you could add an =MATCH() function to check whether at least one of the three (in this case) values exist in the main list of L numbers and then incorporate an =COUNTIF(range of match values,">=1") function to test whether at least one is on your list.

I've assumed here of course that there is just one B5 cell that you want to check. Obviously if there is a whole column B of such cells then you'll probably need a loop in the macro to process each in turn and add the result as necessary to each row in a spare column.

**XOR LX** · 04-15-2014, 04:50 AM

Hi,

If you can post a workbook with a few examples and your desired result in each case I'm sure we'll be able to help.

Regards

**Flash_82** · 04-15-2014, 05:13 AM

I think I added the file now. I added some text as well.

In short, I want to see if a certain code, linked to an item, occurs on a seperate list. If this is the case, it means I have to look further into this item. So I'd like to see an indicator (e.g. "x") in a column in stead of looking through the cell and comparing all the codes to the list.

I'm using Excel2007 and have a next to none knowledge of VBA or macros.

Issue.xlsx

**XOR LX** · 04-15-2014, 05:17 AM

Thanks.

In G2 and copy down:

=IF(COUNT(LOOKUP(2^15,SEARCH(", "&list!$A$1:$A$30&", ",", "&Sheet1!C2&", "))),"x","")

Regards

**Flash_82** · 04-15-2014, 05:22 AM

Originally Posted by Richard Buttrey

Hi,

If the mountain won't come to Mohammed etc......... then maybe you need a macro that will copy B5 to the first column on the row which has no values to the right and then do a Text to Columns on it. Then you could add an =MATCH() function to check whether at least one of the three (in this case) values exist in the main list of L numbers and then incorporate an =COUNTIF(range of match values,">=1") function to test whether at least one is on your list.

I've assumed here of course that there is just one B5 cell that you want to check. Obviously if there is a whole column B of such cells then you'll probably need a loop in the macro to process each in turn and add the result as necessary to each row in a spare column.

The list of "B5" cells is indeed long, actually it's a whole column. The number of items to check is undefined, it depends on the amount of items the client has in stock. Each item has a certain number of codes, some are on the list, others aren't. If it is on the list, I need to take the item to a seperate list and check out other criteria.

So if I have a list of -let's say- 40 items, each having 4-8 codes, it would be helpful to have a function (or funcions) that indicates if that certain item needs to be taken into account, depending on the code.

**AliGW** · 04-15-2014, 05:24 AM

Try this in G2, copied down:

=IF(ISNA(VLOOKUP(C2,list!A:A,1,TRUE)),"","x")

**Flash_82** · 04-15-2014, 05:35 AM

Originally Posted by XOR LX

Thanks.

In G2 and copy down:

=IF(COUNT(LOOKUP(2^15,SEARCH(", "&list!$A$1:$A$30&", ",", "&Sheet1!C2&", "))),"x","")

Regards

Wonderful once again! I presume something went wrong with the "," and ";" but I got it...sorry, you got it to work.

But I'm stumped as to how it works, with the "2^15"? Also, when I break it down, the search function would return a "#VALUE!"-error. I'm really glad I got this to work, would you care to explain this please, so I can understand where I went wrong?

Cheers!

**Flash_82** · 04-15-2014, 05:40 AM

Originally Posted by AliGW

Try this in G2, copied down:

=IF(ISNA(VLOOKUP(C2,list!A:A,1,TRUE)),"","x")

Works like a charm as well! Thank you! Never actually made use of the "ISNA" function before... Interesting and logical in the end

**AliGW** · 04-15-2014, 05:47 AM

Originally Posted by Flash_82

Works like a charm as well! Thank you! Never actually made use of the "ISNA" function before... Interesting and logical in the end

To tell you the truth, I wasn't expecting VLOOKUP to work here, but it does, so I've learnt something today, too.

**XOR LX** · 04-15-2014, 05:50 AM

Bit confused. How can both mine and AliGW's solutions work?

The latter will search for the entire string "L331, L210, L430" in the list tab, whereas mine checks to see if any of the entries in that tab are contained within the string "L331, L210, L430".

Very different, though if both work like you say, and since mine is the more complex of the two, suggest you go with AliGW's.

Regards

**AliGW** · 04-15-2014, 05:56 AM

I wasn't expecting VLOOKUP to work on anything in the string but the first Lnnn code, but it does (I tested it with a non-existent code at the beginning of the string, and it still worked), so it must be reading the commas as separators. Weird! Do feel free to double-check it for me!!!

**XOR LX** · 04-15-2014, 06:42 AM

Remove all three codes - L331, L210, L430 - from the list sheet. What result do you get?

You are using VLOOKUP with a TRUE parameter, so you have to understand what is happening, i.e. that Excel is looking for the closest approximate match to the search string, so it's not at all surprising that it finds at least something that isn't NA()!

Of course, this is not at all the same as saying that this means it is a viable solution!

Regards

**AliGW** · 04-15-2014, 06:56 AM

X means that at least one of those codes exists on the list sheet. It doesn't find anything for cell C3, because none of those values are in the list. See attached. All the OP required, as I understand it, was for the formula to identify if ANY of the codes appeared on that list. I do understand how the VLOOKUP TRUE and FALSE parameters work.

EDIT: Ah - I see what you mean now!!! Only L331 exists on the list, as it happens.

**XOR LX** · 04-15-2014, 07:07 AM

Originally Posted by AliGW

I do understand how the VLOOKUP TRUE and FALSE parameters work.

And yet you suggested such a solution to an OP?!!?

Suggest you do some research! Suffice to say that an "approximate match" for a string such as "L331, L210, L430" on a list which is unsorted is never going to be accurate.

Try replacing the list values with sorted letters of the alphabet: your formula will return "l", since that is the "best" match possible to "L331, L210, L430".

Now randomise that alphabetic list and perform the search again. What result do you get?

Regards

**AliGW** · 04-15-2014, 08:14 AM

Originally Posted by XOR LX

And yet you suggested such a solution to an OP?!!?

Suggest you do some research!

We are all trying to help. We are all human: occasionally, mistakes are made. I'll bet you haven't been completely infallible in your time here.

**XOR LX** · 04-15-2014, 08:31 AM

Originally Posted by AliGW

We are all trying to help. We are all human: occasionally, mistakes are made. I'll bet you haven't been completely infallible in your time here.

But of course! Many, I'm sure! But your post implied that you weren't sure about the formulas involved in your solution prior to posting, which, in my opinion, is somewhat different to posting a solution only to later realise that it contained errors.

The latter is due in general to carelessness, of which we are all at some time guilty; the former, however, would seem to be merely the result of a conscious - and therefore avoidable - choice.

Regards

**AliGW** · 04-15-2014, 08:49 AM

Given the minimal testing I did, the formula seemed to be working. My mistake was publishing it a tad too hastily. As I said, we all make mistakes occasionally. If you feel better about careless mistakes you make than you would about mine, then that's fine by me. I don't feel particularly good about any mistakes that I make, but they do at least provide a learning experience, which is always useful, and I am only human.

**XOR LX** · 04-15-2014, 09:00 AM

Originally Posted by AliGW

...and I am only human.

Are you implying that I am not?

**AliGW** · 04-15-2014, 09:41 AM

Originally Posted by XOR LX

Are you implying that I am not?

I sincerely hope you are ... :-D

**XOR LX** · 04-15-2014, 09:46 AM

Originally Posted by AliGW

I sincerely hope you are ... :-D

01111011001110101101001011111101101001111110111110011101111000001111111010001011111110
1011000001011110111011011011101111100110101111110000111111111110001101000010000101010
10000000111100101010000010001

Searching a single value from multiple values in one cell in a range (or vice versa)

LinkBack

Thread Tools

Rate This Thread

Display

Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Re: Searching a single value from multiple values in one cell in a range (or vice versa)

Thread Information

Users Browsing this Thread

Similar Threads

Assigning Values to Characters and vice versa...

Enter and update ValuesIn Cell1 Based On Values In Cell 2 And Vice Versa.

Transfer Array values to a range and Vice Versa

Transposing Values from Columns into Rows, and Vice Versa

Date to values and vice versa

Bookmarks

Bookmarks

Posting Permissions