Match Index array formula

[vmjan02]

I am not getting the desired out with this array formula (Column D is phone num, Column G is count, column H is D&C, column J is amount

{=IF((Sheet2!$D$5:$D$800000>=$B$3)*(Sheet2!$G$5:$G$800000<=$D$2)*(Sheet2!$H$2:$H$800000=$B$3&$D$2),Sheet2!$J$5:$J$80000)}

In sheet1 it should search B3 (phonenumber) and D2 (count) and should show the amount.


[TABLE="width: 357"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Phon[/TD]
[TD]StID[/TD]
[TD] Country [/TD]
[TD]Count [/TD]
[TD] Phone[/TD]
[TD] CR[/TD]
[TD] Amot[/TD]
[/TR]
[TR]
[TD="align: right"]9768146004[/TD]
[TD="align: right"] 370 [/TD]
[TD] US[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] 97681460041[/TD]
[TD] XX [/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5000[/TD]
[/TR]
</tbody>[/TABLE]

 

[vmjan02]

Have tried this array formula,

={INDEX(Sheet2!$J$5:$J$1041149,SMALL(IF(VALUE($B$3&$D$2)=Sheet2!$H$5:$H$1041149,ROW(Sheet2!$H$5:$H$1041149)-MIN(ROW(Sheet2!$H$5:$H$1041149))+1,""""),ROW(A1)))}

It is only giving me 200, but as I drag the formula then it is giving me [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Num]#Num [/URL] error

C3 200 (array formula is working correctly)
C4 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Num]#Num [/URL] I am not sure is the error because value B3&D3 only once in column H?
C5 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Num]#Num [/URL]
C6 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Num]#Num [/URL]
C7 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Num]#Num [/URL]

Help will be great. Thanks

 

[Aladin Akyurek]

Would you please provide a 5 row input and the desired output for that input without reference to any formula you might have?

 

[vmjan02]

[TABLE="class: cms_table, width: 357"]
<tbody>[TR]
[TD]Phon[/TD]
[TD]StID[/TD]
[TD]Country[/TD]
[TD]Count[/TD]
[TD]Phone[/TD]
[TD]CR[/TD]
[TD]Amot[/TD]
[/TR]
[TR]
[TD="align: right"]9768146004[/TD]
[TD="align: right"]370[/TD]
[TD]US[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]97681460041[/TD]
[TD]XX[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5000[/TD]
[/TR]
</tbody>[/TABLE]



search from col (Phone) Output
97681460041 200 (Cell C1
300 (cell c2)
400 (Cell c3)
5000 (cell c4)

Would you please provide a 5 row input and the desired output for that input without reference to any formula you might have?

 

[Aladin Akyurek]

I'm sorry I don't follow...

Book1
	A	B	C	D	E	F	G
1	Phon	StID	Country	Count	Phone	CR	Amot
2	9768146004	370	US	1	97681460041	XX	200
3							300
4							400
5							5000
Sheet1

What is the data (input) here and what is the output?

 

[vmjan02]

Sheet2 is the data input as table given and output will be if a usr search column E then the output of column G (row 2 to 5) should be available

 

[Aladin Akyurek]

Sheet2 is the data input as table given and output will be if a usr search column E then the output of column G (row 2 to 5) should be available

Please try to answer in terms of the locations post #5 show.

 

[vmjan02]

Search data

[TABLE="width: 405"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Phon[/TD]
[TD]StID[/TD]
[TD]Country[/TD]
[TD]Count[/TD]
[TD]Phone[/TD]
[TD]CR[/TD]
[TD]Amot[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9768146004[/TD]
[TD]370[/TD]
[TD]US[/TD]
[TD]1[/TD]
[TD]97681460041[/TD]
[TD]XX[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9768149002[/TD]
[TD]370[/TD]
[TD]US[/TD]
[TD]1[/TD]
[TD]9768149001[/TD]
[TD]XX[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9768130002[/TD]
[TD]370[/TD]
[TD]US[/TD]
[TD]1[/TD]
[TD]9768130002[/TD]
[TD]XX[/TD]
[TD]600[/TD]
[/TR]
</tbody>[/TABLE]


Output Required from column(J) with respect to search

[TABLE="width: 288"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Search[/TD]
[TD] [/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]User entry[/TD]
[TD="align: right"]97681460041[/TD]
[TD] [/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5000

[/TD]
[/TR]
</tbody>[/TABLE]

current array formula
{=IF((Sheet2!$D$5:$D$800000>=$B$3)*(Sheet2!$G$5:$G$800000<=$D$2)*(Sheet2!$H$2:$H$800000=$B$3&$D$2),Sheet2!$J$5:$J$80000)}
it gives only 1st amount output.


I hope now I am very very clear.

 

[Aladin Akyurek]

What is in B3 and what is in D2?

 

[vmjan02]

B3 is the user search value (search from column D) and d3 is 1 (search from column G)