Lookup partial values and if match return partial value from another cell

**Renejorgensen** · 08-01-2017, 05:15 AM

Hi,

I've been searching what feels like everywhere for a solution. I understand it might not be simple.

I have attached a file containing an example. In the sheet Expected result you can see what value I expect the formula to return in to H2-H6.

The example is short but I need a formula in the H column (Sheet Workbook 1) that does the following:

Take the first word in E-cell, for example E2 and the first number and the last word, in this case New, 6202 and accounting. I then need the formula to search in (Sheet Workbook 2) column D for a match on New, 6202 and accounting. If the formula finds a match I need it to return the value from adjacent C column but removing the searched number, in this case 6202 and only return 10.

The reason I need this setup is that both workbooks contain thousands of rows and the data can be changed from one time to another. But the target number will always be found in the same way.

Anyone that can think of a solution for my problem?

Br
René

**AliGW** · 08-01-2017, 05:30 AM

Something like this:

=LEFT(INDEX('Workbook 2'!F2:$F$8,MATCH('Expected Result'!A2,'Workbook 2'!$E$2:$E$8,0)),2)

**shukla.ankur281190** · 08-01-2017, 05:31 AM

Try

H2

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Or

Formula:

Please Login or Register  to view this content.

**Olly** · 08-01-2017, 06:39 AM

In your example, you can use a straightforward match, as Ali posted.

But from your description, I'm guessing that your real data has non identical cells, which you want to match based only on first word, number and last word.

If my understanding is correct, then both these values should return the same prefix from Workbook 2:

Stockholm cc 8131 Central Projects
Stockholm City Centre 8131 Suburban Projects

So we look up Stockholm | 8131 | Projects, and return prefix 40. Is that correct?

If that logic is sound... then you can do this quite easily using Power Query.

fnRemoveAllText:

Formula:

Please Login or Register  to view this content.

Table1: (load this to your output table)

Formula:

Please Login or Register  to view this content.

Table2: (load to connection only)

Formula:

Please Login or Register  to view this content.

Now you can simply refresh the query to update your output table.

Worked example attached.

**Renejorgensen** · 08-01-2017, 07:18 AM

Thanks you everyone for the examples.

Thread is solved and it works very well!

BR
René

**Renejorgensen** · 08-01-2017, 07:21 AM

Originally Posted by shukla.ankur281190

Try

H2

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Or

Formula:

Please Login or Register  to view this content.

Thank you! The second formula nailed it!

@Olly yes you are right on what I'm trying to achieve. Sadly I don't have Power Query but looking at your attached result it seems to do what I'm trying to achieve.

**Olly** · 08-01-2017, 07:29 AM

Originally Posted by Renejorgensen

@Olly yes you are right on what I'm trying to achieve. Sadly I don't have Power Query but looking at your attached result it seems to do what I'm trying to achieve.

Power Query is a free addin for Excel 2013: https://www.microsoft.com/en-gb/down....aspx?id=39379

I strongly recommend installing it, and learning how to use it.

**Renejorgensen** · 08-01-2017, 07:53 AM

Originally Posted by Olly

Power Query is a free addin for Excel 2013: https://www.microsoft.com/en-gb/down....aspx?id=39379

I strongly recommend installing it, and learning how to use it.

I will raise the question to our IT department

Normally they install all additions and new programs for safety reasons.

Lookup partial values and if match return partial value from another cell

LinkBack

Thread Tools

Rate This Thread

Display

Lookup partial values and if match return partial value from another cell

Re: Lookup partial values and if match return partial value from another cell

Re: Lookup partial values and if match return partial value from another cell

Re: Lookup partial values and if match return partial value from another cell

Re: Lookup partial values and if match return partial value from another cell

Re: Lookup partial values and if match return partial value from another cell

Re: Lookup partial values and if match return partial value from another cell

Re: Lookup partial values and if match return partial value from another cell

Thread Information

Users Browsing this Thread

Similar Threads

VBA code to partial match and return all values

Lookup values in Col A that contain partial value from Col B and return value from Col C

[SOLVED] need to do Partial name search and return the value against the partial name

[SOLVED] Check if Cell Contains any values from a list, partial match

Formula needed to return values for partial or full match

Lookup multiple partial match conditions and return values

Return cell contents on a partial match

Bookmarks

Bookmarks

Posting Permissions