Lookup and Replace with a Table

[MacGyver_excel]

Hi All

I am wondering if anyone can assist me.

I have a situation where I have a table with about 2000 rows. Table A.

I have another table with 100 000 rows. Table B.

There is a column in table B that has a Description. I need to check if any one of those words in the description column match one of my 2000 values in Table A and if it does use that value in a new column in Table B.

Please let me know if you need any further clarification and if you able to assist me. Thank you in advance.

 

[Ron99]

Hi,

why dont you use a vlookup next to table B, or where you want to get the new value if the description exist in table A

for example

table A starts from A1 to B2000

and table B in the next sheet A1 to B 100 000

now where ever you want the data to have the input from the table A just use this formula.

=VLOOKUP(A1,Sheet1!$A$1:$B$2000,2,0)

this is incase if the table is of 2 columns...check the reference

 

[MacGyver_excel]

Thanks for the reply Ron99.

I dont think I am able to use a vlookup because I have to search the cell for the Value. It is not an exact match.

For example:
Table A:
"OHIO"

Table B:
"23 Gordon Street OHIO Bloomington"

I need to check my 2000 values in table A to see if it exists in the Description of Table B. If it does exist I would need to place it in a new column in table B.

 

[Ron99]

Try using this....it will identify the word "Ohio" and the corresponding column value will be shown in table B

=IF(ISNUMBER(FIND("OHIO",A1)),B1,"")

or try to send me the sample of your table...with more description

 

[MacGyver_excel]

Thanks again. This is the formula that I am currently using. The problem is I have to look through a list of 2000 Towns. "OHIO" being one of those towns.

Can I send you some sample data and you should get a better idea of what I am talking about?

Thanks in advance.