index, match in array setup question


J

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
 
Ad

Advertisements

B

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top