Prev: Return range of cell values based on current date
Next: Maintain lowest value in an "external connection" cell
From: jat on 30 Mar 2010 18:16
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

From: Bob Phillips on 30 Mar 2010 19:10
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))

--

HTH

Bob

"jat" <jat(a)discussions.microsoft.com> wrote in message
news:1CA0A012-DE50-45FB-9047-DC05AE3C6026(a)microsoft.com...
> 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
>


 | 
Pages: 1
Prev: Return range of cell values based on current date
Next: Maintain lowest value in an "external connection" cell