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.

Phon	StID	Country	Count	Phone	CR	Amot
9768146004	370	US	1	97681460041	XX	200
						300
						400
						5000

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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]

Phon	StID	Country	Count	Phone	CR	Amot
9768146004	370	US	1	97681460041	XX	200
						300
						400
						5000

<tbody>
</tbody>

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

	D	E	F	G	H	I	J
1	Phon	StID	Country	Count	Phone	CR	Amot
2	9768146004	370	US	1	97681460041	XX	200
3							300
4							400
5							5000
1	9768149002	370	US	1	9768149001	XX	100
2							200
1	9768130002	370	US	1	9768130002	XX	600

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Output Required from column(J) with respect to search

	Search		Output
User entry	97681460041		200
			300
			400
			5000

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

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)