How do I return the intersecting value from 2 partial match lookups? Index/Match

Question

I have two tables in an excel worksheet. I'm trying to gather product info from data on another table in the same workbook. The first table is the product data feed I'm building with the product part numbers. Those part numbers include the variables of the product (in this case the length and the width). On the other sheet, I have partial part numbers in the header column and the rough dimensions in the header row. The intersection gives the final dimensions which is the data I'm trying to gather on sheet 1. I've been trying to use and Index/Match formula to solve the problem, but since there are only partial part numbers on the 2nd sheet the lookup is inconclusive. I know the lookup value supports wildcards, but it seems I would need some sort of wildcard search within the lookup array instead.

Example product names on sheet 1 column A "EXP81285-150-11 x 14-Flat"

Example of product names on sheet 2 column A "EXP81285-150" Example of rough dimensions on sheet 2 row 1 "11 x 14"

Here is what I have so far:

=INDEX('sheet 2'!$A$1:$L$87,MATCH($A3,'sheet 2'!$A:$A,0),MATCH($A3,'sheet 2'!$1:$1,0))

Sheet 1

Sheet 2

Any help is greatly appreciated!

I tried to add images of the excel files but I dont have enough reputation points yet — Brian Hassett
Upload to a site like imugr and provide link in your original post. — Scott Craner

Dirk Reichel Dirk Reichel · Accepted Answer · 2015-11-18T16:29:48

Asuming its always like string1-string2-unused and string2 and unused doesn't contain "-" you can get the first string with:

*updated due to misunderstanding*
=MID(A3,4,FIND("|",SUBSTITUTE(A3,"-","|",LEN(A3)-LEN(SUBSTITUTE(A3,"-",""))-1))-4)

While the string2 one is a hell of a formula:

=MID(A3,FIND("|",SUBSTITUTE(A3,"-","|",LEN(A3)-LEN(SUBSTITUTE(A3,"-",""))-1))+1,FIND("|",SUBSTITUTE(A3,"-","|",LEN(A3)-LEN(SUBSTITUTE(A3,"-",""))))-FIND("|",SUBSTITUTE(A3,"-","|",LEN(A3)-LEN(SUBSTITUTE(A3,"-",""))-1))-1)

Asuming the last part is allways in Q3 then:

=MID(SUBSTITUTE($A3,"-"&$Q3,""),FIND("|",SUBSTITUTE($A3,"-","|",LEN($A3)-LEN(SUBSTITUTE($A3,"-",""))-1))+1,99)

You may also use an arrayformula for the second part like:

=MID(SUBSTITUTE($A3,"-"&$Q3,""),LARGE((MID($A3,ROW($1:$99),1)="-")*ROW($1:$99),2)+1,99)

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

(the second formula may work faster)
You could use array formulas in a reverse Match however... having lots of entrys even one formula will slow down the calculation by ~2-5 seconds.

You better use VBA like:
(in Module)

Public Function MATCH2(str As String, rng As Range) As Long
  Dim i As Long, var1 As Variant
  i = 0
  For Each var1 In rng
    i = i + 1
    If InStr(str, var1.Value) Then MATCH2 = i: Exit Function
  Next
End Function

And then use your formula as followed:

=INDEX('sheet 2'!$A$1:$L$87,MATCH2($A3,'sheet 2'!$A:$A,0),MATCH2($A3,'sheet 2'!$1:$1,0))

EDIT 2015-11-19

OK... some small problems:

some sizes doesnt exist (like 6 x 9)
size 7 x 12 was bugged (a space at the end > fixed it)
the function needs to be in a module (also fixed that)
also some items doesn't exist like 600823-002
a misunderstanding regarding the formulas (doesn't matter at the VBA-version) > all asumed the A:A-searchstring starts at the 1st character (but it is the 4th, no EXP)

Also there will be an error at each "header" (the ones without the * x * but that should be ok)

You can download the updated workbook here
If you still have questions, just ask :)

How do I return the intersecting value from 2 partial match lookups? Index/Match

2 Answers