index, match in array setup question

[jat]

good day,

i am working on trying to do an index/match within an array. the formula i
have is:

=INDEX(Sheet1!C:C,MATCH(1,(Sheet1!A:A=A5)*(Sheet1!B:B=Sheet2!B5),0)) with
the { } around the formula.

the problem that i want to over come is as follows:
on the worksheet that i enter the formula, column A (cell A5 in the formula
above) can have an item or critera that is anywhere from 1 to 13 characters
long. On sheet 1, where i will be pulling the data from, it sources from
Column A (A:A) but the report will only show the first 10 characters (from 1
up to 10, if longer than 10, the last three characters are dropped).

The second column can be an exact match but this column can have duplicates.
how can the formula be adjusted so that it matches the up to the first ten
characters in column A, and an exact match in Column B?

anything to get me going in the right direction would help.

thank you,

jat

 

[Bob Phillips]

I hope you have Excel 2007

=INDEX(Sheet1!C:C,MATCH(1,(Sheet1!A:A=MID(A5,1,MAX(10,A5))*(Sheet1!B:B=Sheet2!B5),0))