VLOOKUP with multiple items in one cell?

[satch413]

Hello,

I'm trying to look up a specific code within a list of many other codes. The problem is that many of the codes share a cell because they correspond to the same data I'm trying to move over.

For example, I'm trying to look up the sic code '3271-0000' but the code exists in a cell that also contains 9 other 8 digit sic codes that all correspond to the same market. Is it possible to look up this specific code even though the code in the lookup table shares a cell with multiple other codes?

Thanks!

[Glenn Kennedy]

yes.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[satch413]

yes.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I've attached a sample file. I'm trying to populate columns G and H on 'SALES RAW DATA' with data in columns K and L on 'NEW WITH MAP'

[Glenn Kennedy]

In G2, copied across and down:

=IFERROR(INDEX('NEW WITH MAP'!K$2:K$200,MATCH("*"&$C2&"*",'NEW WITH MAP'!$F$2:$F$200,0)),"")

[satch413]

In G2, copied across and down:

=IFERROR(INDEX('NEW WITH MAP'!K$2:K$200,MATCH("*"&$C2&"*",'NEW WITH MAP'!$F$2:$F$200,0)),"")

Thank you but G2 is where the titles are, not the first line of data so this doesn't make sense. Also there are many blanks in columns G and H when there shouldn't be any because all of the codes have matching corresponding codes in the look up table.

Thank you for the attempt. This is clearly beyond my skill set.

[Glenn Kennedy]

Add some expected results, calculated manually, and repost.

[Glenn Kennedy]

I was pretty sure it was OK. I simply started one row up. G3 copied down.

=IFERROR(INDEX('NEW WITH MAP'!K$2:K$200,MATCH("*"&$C3&"*",'NEW WITH MAP'!$F$2:$F$200,0)),"")

[satch413]

Add some expected results, calculated manually, and repost.

I manually added expected results up to row 29. There shouldnt be any data in G2 or H2 because its pulling from a blank cell (C2)

[Glenn Kennedy]

Grrr. It took me a while to find my mistake in this formula... it was taking forever to calculate...

=IFERROR(INDEX('NEW WITH MAP'!K$2:K$200,SUMPRODUCT((ISNUMBER(SEARCH($C3,'NEW WITH MAP'!$F$2:$F$200)))*(ROW($F$2:$F$200)-ROW($F$2)+1))),"")

[satch413]

Grrr. It took me a while to find my mistake in this formula... it was taking forever to calculate...

=IFERROR(INDEX('NEW WITH MAP'!K$2:K$200,SUMPRODUCT((ISNUMBER(SEARCH($C3,'NEW WITH MAP'!$F$2:$F$200)))*(ROW($F$2:$F$200)-ROW($F$2)+1))),"")

I sincerely appreciate your effort on this but it still isn't correct. Based on the lookup table Cell G10 should be AEROSPACE not DEFENSE. I'm assuming there are other issues as well. I really appreciate your help but unfortunately I think I will have to resort to separating all of the codes into individual cells.

[satch413]

Grrr. It took me a while to find my mistake in this formula... it was taking forever to calculate...

=IFERROR(INDEX('NEW WITH MAP'!K$2:K$200,SUMPRODUCT((ISNUMBER(SEARCH($C3,'NEW WITH MAP'!$F$2:$F$200)))*(ROW($F$2:$F$200)-ROW($F$2)+1))),"")

If you could somehow fix this it would be insanely appreciated. I am just not familiar with the functions you're using so if not I will have to just split them up.

Regardless, +rep to you Glenn. A valiant effort as it stands. Thanks again.

[Glenn Kennedy]

Back in an hour...

[satch413]

Back in an hour...

You're amazing sir. Another error I found was G30 and G31 should not be in the power generation segment. If the lookup table is column F on 'NEW WITH MAP' then G30 and G31 should be blank because the matching SIC codes are in cell G80 of 'NEW WITH MAP'

[Glenn Kennedy]

Funny... twice in one day. I am having the same issue in another, similar, thread. Except that there there are 62,000 rows of dta...

So. I have fixed G30/31. Silly me. I left a step out.

G10 is wrong becasue there are DUPLICATE entries for it (3728-0203 occurs in F2 and in F3). This is not caused by me mucking around, as the duplicate also exists on your first sheet. Can you confirm thta duplicates can occur? If so, do you want the first of the duplicate values, or the last, as your result?