Extract Data based on a Partial Match

**andrewc** · 07-16-2020, 09:17 AM

Hi,

In column A in the attached workbook I have list of Product Code.

I would like to create a formula in column B that will return the Alternative Code from column D.

The rule for this it that if the first two characters of the Product Code are "AB" or "AC", the formula should return the Alternative Code that ends with the same 6 digits.

In my example, "AB123456" starts with "AB" so an Alternative Code that ends with 123456 should be returned, "GG123456" in this case.

Can someone please suggest a formula to accomplish this?

Thanks!

**jeffreybrown** · 07-16-2020, 09:22 AM

Maybe this is an option

=INDEX($D$2:$D$3,MATCH(RIGHT(A2,6),RIGHT($D$2:$D$3,6),0))

IMPORTANT

This is an array formula
Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
If entered correctly, the formula will be enclosed in {brackets}
Do not enter the {brackets} manually

**Andy Pope** · 07-16-2020, 09:32 AM

One possible way,

=IF(OR(UPPER(LEFT(A2,2))="AC",UPPER(LEFT(A2,2))="AB"),INDEX($D$2:$D$3,MATCH("*" & RIGHT(A2,6),$D$2:$D$3,0)),A2)

**andrewc** · 07-16-2020, 03:01 PM

Hi,

A little twist on this:

Is it possible to adjust Andy's formula to accommodate the situation where the range $D$2:$D$3 could be found in all, not all or none of Sheet1, Sheet2, Sheet3 and Sheet4. The formula would consider the range in each of these 4 sheets and return an Alternative Code when a match is made in one of the 4 sheets?

Thanks!

**andrewc** · 07-16-2020, 03:02 PM

Thanks very much, Andy & Jeff!

**Andy Pope** · 07-17-2020, 02:22 AM

You would need to build a nested formula in order to test 4 different ranges.

It would be easier to build a single table from the 4 separate ranges

**bebo021999** · 07-17-2020, 03:30 AM

Hi Andrewc,
Andy's hard code "AB" & "AC"
A little twist with Jeff's to be non-array formula:

Please Login or Register  to view this content.

Enter only.

Extract Data based on a Partial Match

LinkBack

Thread Tools

Rate This Thread

Display

Extract Data based on a Partial Match

Re: Extract Data based on a Partial Match

Re: Extract Data based on a Partial Match

Re: Extract Data based on a Partial Match

Re: Extract Data based on a Partial Match

Re: Extract Data based on a Partial Match

Re: Extract Data based on a Partial Match

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Extract matches based on partial criteria in one column and exact match on another column

[SOLVED] Filter and extract list of data based on criteria using INDEX MATCH

[SOLVED] VLOOKUP based on partial match

find data based on partial match

Macro| Search 4 partial match then apply IF statement based on blank cell next to match ?

Automatically search and copy data from one sheet to another based on partial match

sumif based on partial match

Bookmarks

Bookmarks

Posting Permissions