INDEX AND MATCH QUERY ?

[kate]

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'!D4293)))
IS THIS RIGHT BECAUSE IT DOES NOT WORK AND IF NOT PLEASE HELP IT IS DRIVING
ME CRAZY ........
THANKYOU

 

[Toppers]

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'!D4293="C"),0))

HTH

 

[Toppers]

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