Partial match using LEFT() in combination with MATCH

Perhaps?

=INDEX($b$2:$B$1000,MATCH(LEFT(D2,5),$A$2:$A$1000,FALSE),2)

Or even better.

=INDEX($b$2:$B$1000,MATCH(LEFT(D2,5),$A$2:$A$1000,0))

That should work if I had only text or numbers in a cell, but I have both
Some of the codes I'll be matching look like this:

1320G-2
1131X-3

I've allso noticed that while cleaning up the formula and pulling in the data from different sheets, I can't include the ROW funtion tag any more.

This works
=INDEX('Spec list'!$A$2:$B$1000,MATCH(LEFT('MASTER LINELIST'!J13,5)+0,'Spec list'!$A$2:$A$1000,FALSE),2)

But this doesn't
=INDEX('Spec list'!$A$2:$B$1000,MATCH(LEFT('MASTER LINELIST'!J:J,ROW(),5)+0,'Spec list'!$A$2:$A$1000,FALSE),2)

I get the message, to many arguments. I'm begining to wonder if it might me a good idea to create a calculation sheet to grab all the data so I can use smaler formula's nsteed of cramming it all into one formula.

Originally Posted by Jonmo1

But column A would be
1320G
1131X

=INDEX($A$2:$B$1000,MATCH(IF(ISNUMBER(LEFT(D2,5)+0),LEFT(D2,5)+0,LEFT(D2,5)),$A$2:$A$1000,FALSE),2)

I'll try an wrap my head around this one. As a real greenhorn that just started playing with Excel yesterday I need some time to figure out what a function actually does and how you use it.

If I understand correctly, you say the following, if the first 5 characters are a number, then use LEFT(D2,5)+0, else use LEFT(D2,5). As a value of 1302G can not be converted into a number, you can then look for a text string?

I've tested your code, and it works in most of the cases. I now run into an N/A output when I use match search for a value with 6 figures or more.

IE:

Works:
1302G-2
1302G-3
61011-222
31011-2235x

Doesn't work:
251011

As soon as I use 6 or more consecutive numbers, the formula doesn't work. This surprises me as the formula looks at the first 5 characters right?

No problem, I've put all the data I need to check against into a clean file.

Depending on the input in Col D, I want to crosscheck with Col A and give the material in Col B as output.

Book1.xls

I Agree I need to get a grip on the data in Col A to begin with. Inconsistant data is a nightmare.

Your first D2 example was 11011-2, when you have 6 digits is that everything before the dash? If so then try using this fomula to pull everything before the dash (or the whole thing if there is no dash):

=LEFT(D2,FIND("-",D2&"-")-1)

then incorporate that in your INDEX/MATCH formula like this

=INDEX($A$2:$B$1000,MATCH(LEFT(D2,FIND("-",D2&"-")-1),INDEX($A$2:$A$1000&"",0),0),2)

That will cope with both text and numbers because it leaves the lookup value as text but converts the lookup range to text to match (and the lookup value can be any number of characters)

Originally Posted by daddylonglegs

Your first D2 example was 11011-2, when you have 6 digits is that everything before the dash? If so then try using this fomula to pull everything before the dash (or the whole thing if there is no dash):

That is correct, al the data after the " -" isn't relavant as it doesn' t change anything about the material to use.

Your suggestion made al my problems go away. That realy did the trick. I'll now go and spend some time to figure out what you exactly did.
I can't thank you enough for your help. To bad you don't live nearby otherwise I'd buy you a pint or two (maybe even three )

Originally Posted by Jonmo1

Given that sample book, why are we bothering with the left function at all??
Why not just
=VLOOKUP(D2,A:B,2,FALSE)

Because when I try to match with 32010-2x I get an error. It's my bad though, because I forgot to include some search possibilities.

This is more like it:

For Col D (search):
11011
11072
31015
32010-2x
151071-2x
1131X
1302G
91071
11055

Am I pushing it if I tried to replace the D2 with the ROW()? The reason for this, is that later on I'll be using the search formula in aprox 100 rows and I don't want output to get messed up if new rows are inserted inbetween existing rows.

If everything after the "-" is irrelevant, you can use =LEFT(D2,FIND("-",D2&"-")-1) as daddylonglegs suggested.
It's saying "Check string D2, start from the left, number of character is from first letter to the first dash from the left, then minus one (the dash)"
And there we go, a string consists of everything before the dash.
Just to make sure, you won't have anymore dashes right, because it will stop at the first dash.