Selecting cell from another sheet based on text value

I have one sheet with the range of cells with text in them, and another sheet with the range of cells with text in them and the number value next to the every cell.
I need to choose the number value from the 2nd sheet based on the text from the 1st sheet matching some parts of text from the 2nd sheet.
What functions should I combine in order to achieve that? Sorry if my post is confusing, and sorry for my English as well.

Here is the simplified example so you can better understand me:
Sheet1: A1: "excelforum 149 excel help beginner guru"; B1: (I need value from B2 cell from Sheet2 here)
Sheet2: A1: "excelforum 132 help beginner", B1: 300; A2: "excelforum 149 help beginner", B2: 111; A3: "excelforum 149 guru", B3: 399

INDEX / MATCH should do the trick. Something like:

Formula:

Please Login or Register  to view this content.

Adapt references to suit your workbook.

Welcome to the forum.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

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

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as 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.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Olly - FYI the OP mentioned partial matches, so I asked for a workbook so that we can ascertain the nature of these.

Ok, here is the sample of the data

Sometimes the text between the two sheets doesn't match at all, so it would be good if I could define additional keywords which should return particular value from sheet2, beside the text from the B column from the sheet1.

Your data sample doesn't help me figure out what you're trying to achieve... I can't see the matches you expect.

Administrative Note:

Welcome to the forum.

We would love to help you with your query; however, it has been brought to our attention the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and read this to understand why we (and other sites like us) consider this to be important.

Link to x-post has been added for you this time, but please do so in the future.

x-post: https://www.mrexcel.com/forum/excel-...ext-value.html

Originally Posted by Olly

Your data sample doesn't help me figure out what you're trying to achieve... I can't see the matches you expect.

I want to get value from Sheet2 G column, based on partial match between text from Sheet1 B column and Sheet2 C column. I would also add another column in Sheet2 with keywords, in order to make matching easier, because sometimes the text between Sheet1 B column and Sheet2 column is completely different.

Destination column is Sheet1 G column.


Value from Sheet2 G column into Sheet1 G column based on match between Sheet1 B column and Sheet2 C column + "Keyword" column (column I):


EDIT: Uploaded edited sample, it should be easier to understand.

Nikola - did you read and understand post #7? I ask because you have now cross-posted on yet another forum, and you haven't provided cross-post links there or here.

No help to be given until the new cross-post link has been added.

You can tell us the forums you have posted to - that is what you must do, please.