Help on multiple criteria for formula and return a date

Vickaa · External Since: Apr 25, 2012 Posts: 1

Hi all, I am new on your site and need help please. The formula should
do the following;- Take Book 1, Sheet 1, A4 and B3, find them in Book 2
,Sheet 1,A3 to A 1000, and B 3 to B 1000, and return the date in Book 2
sheet1, colomb C3.
A B C
1 Book 1 Sheet 1
2 Activity Code Activity Code
3 Coy number Q50012077-67 Q50012587-05
4 20017835 formula???
5 20017870
6 20017916
7 20021864
8
9
10

A B C
1 Book 2 Sheet 1
2 Emp No Activity Code Start Date
3 20017703 Q50012077-67 2012/04/15
4 20017828 Q50012587-05 2012/04/10
5 20024161 Q50012365-11 2012/04/18
6 20024332 BECSA08407-01 2012/04/12
7 20020819 BECSA08407-01 2012/04/12
8 20020016 Q500123501 2012/04/20

--
Vickaa

isabelle · External Since: May 27, 2011 Posts: 38

hi Vickaa,

it is an array formula to validate with ctrl + maj + enter

=INDEX('[Book2.XLS]Sheet1'!$C1:$C500;EQUIV($A2&B$1;'[Book2.XLS]Sheet1'!$A1:$A500&'[Book2.XLS]Sheet1'!$B1:$B500;0))

--
isabelle

Le 2012-04-25 10:14, Vickaa a écrit :
> Hi all, I am new on your site and need help please. The formula should
> do the following;- Take Book 1, Sheet 1, A4 and B3, find them in Book 2
> ,Sheet 1,A3 to A 1000, and B 3 to B 1000, and return the date in Book 2
> sheet1, colomb C3.
> A B C
> 1 Book 1 Sheet 1
> 2 Activity Code Activity Code
> 3 Coy number Q50012077-67 Q50012587-05
> 4 20017835 formula???
> 5 20017870
> 6 20017916
> 7 20021864
> 8
> 9
> 10
>
> A B C
> 1 Book 2 Sheet 1
> 2 Emp No Activity Code Start Date
> 3 20017703 Q50012077-67 2012/04/15
> 4 20017828 Q50012587-05 2012/04/10
> 5 20024161 Q50012365-11 2012/04/18
> 6 20024332 BECSA08407-01 2012/04/12
> 7 20020819 BECSA08407-01 2012/04/12
> 8 20020016 Q500123501 2012/04/20
>
>
>
>

Vickaa · External Since: May 14, 2012 Posts: 1

Many thanks Isabelle
isabelle;1601249 Wrote:
> hi Vickaa,
>
> it is an array formula to validate with ctrl + maj + enter
>
> =INDEX('[Book2.XLS]Sheet1'!$C1:$C500;EQUIV($A2&B$1;'[Book2.XLS]Sheet1'!$A1:$A500&'[Book2.XLS]Sheet1'!$B1:$B500;0))
>
> --
> isabelle
>
>
>
> Le 2012-04-25 10:14, Vickaa a écrit :-
> > Hi all, I am new on your site and need help please. The formula
> should
> > do the following;- Take Book 1, Sheet 1, A4 and B3, find them in Book
> 2
> > ,Sheet 1,A3 to A 1000, and B 3 to B 1000, and return the date in Book
> 2
> > sheet1, colomb C3.
> > A B C
> > 1 Book 1 Sheet 1
> > 2 Activity Code Activity Code
> > 3 Coy number Q50012077-67 Q50012587-05
> > 4 20017835 formula???
> > 5 20017870
> > 6 20017916
> > 7 20021864
> > 8
> > 9
> > 10
> >
> > A B C
> > 1 Book 2 Sheet 1
> > 2 Emp No Activity Code Start Date
> > 3 20017703 Q50012077-67 2012/04/15
> > 4 20017828 Q50012587-05 2012/04/10
> > 5 20024161 Q50012365-11 2012/04/18
> > 6 20024332 BECSA08407-01 2012/04/12
> > 7 20020819 BECSA08407-01 2012/04/12
> > 8 20020016 Q500123501 2012/04/20
> >
> >
> >
> >-

--
Vickaa