INDEX AND MATCH QUERY ?

I have been trying to do a vlookup for multiple data i have already asked
this question on this forum and the response was to use index and match which
I have been trying to do but it stoll does not work.
My query is on excel;
A B C D E F
G
1 AA ONC CND C N 2
2 AA ONC DNA C N 1 2
3 AA ONC H C N 1 1
4 ABWT GYN ATT C N 31 36
5 ABWT GYN CND C N 1 3
6 ABWT GYN DNA C N 3 1
7 AC ORT ATT C N 91 55
8 AC ORT CND C N 1
9 AC ORT DNA C N 6
10 AC ORT H C N 1 1


I WANT THE NUMBER IN COLOMN G IF THE FOLLWING CRITERIA MATCHES
AA ONC ATT C

OR

AC ORT ATT C

MY FORMULA IS :INDEX('Outpatients New'!F4:F293,MATCH(A1,'Outpatients
New'!A4:A293)=MATCH(B1,'Outpatients New'!B4:B293)=MATCH("ATT",'Outpatients
New'!C4:C293)=MATCH("C",'Outpatients New'!D4:D293)))
IS THIS RIGHT BECAUSE IT DOES NOT WORK AND IF NOT PLEASE HELP IT IS DRIVING
ME CRAZY ........
THANKYOU

Enter this with Ctrl-Shift-Enter as an array formula ... you will see {}
round the formula if it is entered correctly:

=INDEX('Outpatients New'!F4:F293,MATCH(1,('Outpatients
New'!A4:A293=A1)*('Outpatients New'!B4:B293=B1)*('Outpatients
New'!C4:C293="ATT")*('Outpatients New'!D4:D293="C"),0))

HTH

"kate" wrote:

> I have been trying to do a vlookup for multiple data i have already asked
> this question on this forum and the response was to use index and match which
> I have been trying to do but it stoll does not work.
> My query is on excel;
> A B C D E F
> G
> 1 AA ONC CND C N 2
> 2 AA ONC DNA C N 1 2
> 3 AA ONC H C N 1 1
> 4 ABWT GYN ATT C N 31 36
> 5 ABWT GYN CND C N 1 3
> 6 ABWT GYN DNA C N 3 1
> 7 AC ORT ATT C N 91 55
> 8 AC ORT CND C N 1
> 9 AC ORT DNA C N 6
> 10 AC ORT H C N 1 1
>
>
> I WANT THE NUMBER IN COLOMN G IF THE FOLLWING CRITERIA MATCHES
> AA ONC ATT C
>
> OR
>
> AC ORT ATT C
>
> MY FORMULA IS :INDEX('Outpatients New'!F4:F293,MATCH(A1,'Outpatients
> New'!A4:A293)=MATCH(B1,'Outpatients New'!B4:B293)=MATCH("ATT",'Outpatients
> New'!C4:C293)=MATCH("C",'Outpatients New'!D4:D293)))
> IS THIS RIGHT BECAUSE IT DOES NOT WORK AND IF NOT PLEASE HELP IT IS DRIVING
> ME CRAZY ........
> THANKYOU
>

..... If you want data from column G, change the F4:F293 to G4:G293 and you
should set the ranges to be absolute address i.e. $A$4:$A$293 etc

=INDEX('Outpatients New'!$G$4:$G$293,MATCH(1,('Outpatients
New'!$A$4:$A$293=A1)*('Outpatients New'!$B$4:$B$293=B1)*('Outpatients
New'!$C$4:$C$293="ATT")*('Outpatients New'!$D$4:$D$293="C"),0))

"Toppers" wrote:

> Enter this with Ctrl-Shift-Enter as an array formula ... you will see {}
> round the formula if it is entered correctly:
>
> =INDEX('Outpatients New'!F4:F293,MATCH(1,('Outpatients
> New'!A4:A293=A1)*('Outpatients New'!B4:B293=B1)*('Outpatients
> New'!C4:C293="ATT")*('Outpatients New'!D4:D293="C"),0))
>
> HTH
>
> "kate" wrote:
>
> > I have been trying to do a vlookup for multiple data i have already asked
> > this question on this forum and the response was to use index and match which
> > I have been trying to do but it stoll does not work.
> > My query is on excel;
> > A B C D E F
> > G
> > 1 AA ONC CND C N 2
> > 2 AA ONC DNA C N 1 2
> > 3 AA ONC H C N 1 1
> > 4 ABWT GYN ATT C N 31 36
> > 5 ABWT GYN CND C N 1 3
> > 6 ABWT GYN DNA C N 3 1
> > 7 AC ORT ATT C N 91 55
> > 8 AC ORT CND C N 1
> > 9 AC ORT DNA C N 6
> > 10 AC ORT H C N 1 1
> >
> >
> > I WANT THE NUMBER IN COLOMN G IF THE FOLLWING CRITERIA MATCHES
> > AA ONC ATT C
> >
> > OR
> >
> > AC ORT ATT C
> >
> > MY FORMULA IS :INDEX('Outpatients New'!F4:F293,MATCH(A1,'Outpatients
> > New'!A4:A293)=MATCH(B1,'Outpatients New'!B4:B293)=MATCH("ATT",'Outpatients
> > New'!C4:C293)=MATCH("C",'Outpatients New'!D4:D293)))
> > IS THIS RIGHT BECAUSE IT DOES NOT WORK AND IF NOT PLEASE HELP IT IS DRIVING
> > ME CRAZY ........
> > THANKYOU
> >