get record from other sheets ...use index match

Dear All,

STEP 1 : AT MOST THERE IS 6 RECORDS WHICH TAKE FROM SHEET 2, each records which is not only 1 records,
same date should have more than 1 records ,...sometimes ..there is 4,5,6 records...

STEP 2 : THE RECORD WHICH SHOULD MATCH COLUMN B,C,D FROM SHEET 2
SHEET 1 COLUMN B SHOULD MATCH SHEET 2 COLUMN F
SHEET 1 COLUMN C SHOULD MATCH SHEET 2 COLUMN A
SHEET 1 COLUMN D SHOULD MATCH SHEET 2 COLUMN G

F1=INDEX(Sheet2!$A$1:$A$17000,MATCH(1,(Sheet2!$F$1:$F$17000=B1)*(Sheet2!$A$1:$A$17000=C1),0)*
(Sheet2! $A$1:$A$17000=D1),0))
There is also error …

attached pls find file nameQ&A
The answer is same as the aswer sheet …please help

Thanks
Tom

Try

=INDEX(Sheet2!$A$1:$A$17000,MATCH(1,(Sheet2!$F$1:$F$17000=B1)*(Sheet2!$A$1:$A$17000=C1)*(Sheet2!$G$1:$G$17000=D1),0))

Enter with Ctrl+Shift+Enter

Dear Sir,

Please find attached Q&A revised file

N1=INDEX(Sheet2!$H$1:$H$17000,MATCH(1,(Sheet2!$F$1:$F$17000=B6)*(Sheet2!$A$1:$A$17000=C6)*(Sheet2!$G$1:$G$17000=D6),0))
this forumula should get n1=1

above forumula is wrong

N2= what is the forumula, the answer is n2=2

Thanks
Tom

Why D6 when you are comparing row 1?

=INDEX(Sheet2!$H$1:$H$17000,MATCH(1,(Sheet2!$F$1:$F$17000=B1)*(Sheet2!$A$1:$A$17000=C1)*(Sheet2!$G$1:$G$17000=D1),0))

returns 1

for n2 the value is 1 Check the data you are comparing:

200 06/07/2016 USA in sheet2 returns a 1 from column H (row 31)

Hi ..

when
100,2016/7/5,USA,100 THERE SHOULD BE 9 RECORDS. NOT 1 RECORD RETURN
200,2016/7/6,USA,200 THERE SHOULD BE 3 RECORDS . NOT 1 RECORD RETURN
So the formula is ..?
tks
tom

In N1

=IFERROR(INDEX(Sheet2!$H$1:$H$17000,SMALL(IF((Sheet2!$F$1:$F$17000=$B$1)*(Sheet2!$A$1:$A$17000=$C$1)*(Sheet2!$G$1:$G$17000=$D$1),ROW($H$1:$H$1700)-ROW($H$1)+1,""),ROWS($H$1:H1))),"")

Enter will Ctrl+ShifT+Enter

Drag down

Repeat changing highlighted ranges for other combinations

Hi, The answer is correct with thanks. how about the formula in

o1?
f11?
Thanks
tom

Hi, The answer is correct with thanks. how about the formula in

o1?
f11?
Thanks
tom

oh ..it solved