Vlookup value if contained in arey

huss1212

New Member
Joined
Oct 4, 2016
Messages
2
hi there,
i have a sistematic problem with data and i have to be creative with exported data for them to be usable.

i deal with products that have certain ID
problem is One product can have MORE diffreretnt ID's. I coded an export to combine all ID' that refer to same product in one CELL (ref. CODED TABLE)

so product 1 can have |x y z| ID's. product ID's have few spaces between them
i pull this export every quarter and keep up with table updating for unique rows for one product.

now, exported table has all unique ID's, so more ID's can refer to ONE product.


when i want to compare my Coded table made on 01.01. with my newly exported table on 28.02.
i need my unique ID's from exported table vlooked up in Coded table and have the update done from expotred table. problem is I cannot firure out the formula to look for 1 ID' within an arey where cells containe more ID's


[TABLE="width: 500"]
<tbody>[TR]
[TD]A:A
Coded table ID's[/TD]
[TD]B:B
Exported ID's[/TD]
[TD]column i need Coded table ID's found from Exported ID's table.[/TD]
[TD][/TD]
[TD]Returned value should be regarding B column:[/TD]
[/TR]
[TR]
[TD]123 234 345 456[/TD]
[TD]123[/TD]
[TD]=vlookup("*"&B2&"*";A:A;1;false)[/TD]
[TD]formula doesn't work[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]321 432 543 654[/TD]
[TD]234[/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]741 852[/TD]
[TD]741[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[/TR]
[TR]
[TD]963 852 741[/TD]
[TD]963[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]654[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]321[/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]543[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]852[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi, welcome to the board.

Your request is a bit unclear, but I think I understand what you want to do.

I can't solve this for you, but I would suggest that grouping all of your IDs for the same product into a single cell, and THEN trying to perform lookups on single IDs located somewhere in that cell is not a good idea.
I would strongly recommend looking for a different solution.
For instance, what about a lookup table where you simply store all the relationships between the multiple product codes ?
 
Upvote 0
Strange. It works for me:


Book1
ABC
1IDsExported IDsMatch
2123 234 345 456123123 234 345 456
3321 432 543 654234123 234 345 456
4741 852741741 852
5963 852 741963963 852 741
6654321 432 543 654
7456123 234 345 456
8321321 432 543 654
9543321 432 543 654
10852741 852
Sheet1
Cell Formulas
RangeFormula
C2=VLOOKUP("*"&$B2&"*",A:A,1,FALSE)


WBD
 
Upvote 0
Thank you WBD.

i have to mention that I use EU execl so i have to use ";" instead of ",".

so there is a ghost in my excel, because when i make my own table and formula - it doesn't work.

when i copied your table and formula, and adjusted cells, links and ";" it works.

i have 2 tables side by side, one works and one doesn't. seems your kung fu is stronger than mine.
no idea what 23 grams of soul are copied from your table.

anyway thanks again
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top