Match Index

[vmjan02]

Have this table in sheet 2

[TABLE="width: 1219"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col span="2" style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD][TABLE="width: 1219"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col span="2" style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Fname[/TD]
[TD="align: center"]Lname[/TD]
[TD="align: center"]Customer Number[/TD]
[TD="align: center"]Total Amount Paid[/TD]
[TD="align: center"]Area[/TD]
[TD="align: center"]Amount Rank[/TD]
[TD="align: center"]Match[/TD]
[TD="align: center"]Employee[/TD]
[TD="align: center"]Services[/TD]
[/TR]
[TR]
[TD="align: center"]01-Dec-2018,12:35:25[/TD]
[TD="align: center"]Anil[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9833998264[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]Vashi[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]98339982641[/TD]
[TD="align: center"]Irfan[/TD]
[TD="align: center"]Tax80[/TD]
[/TR]
[TR]
[TD="align: center"]02-Dec-2018,12:45:01[/TD]
[TD="align: center"]Ankit[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]8975295430[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]Vashi[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]89752954301[/TD]
[TD="align: center"]Irfan[/TD]
[TD="align: center"]TAX1[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Tax11[/TD]
[/TR]
[TR]
[TD="align: center"]02-Dec-2018,12:45:01[/TD]
[TD="align: center"]Rafia[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]8668062641[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]Vashi[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]86680626411[/TD]
[TD="align: center"]Irfan[/TD]
[TD="align: center"]Tax14[/TD]
[/TR]
[TR]
[TD="align: center"]02-Dec-2018,13:29:04[/TD]
[TD="align: center"]Virender[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9416340744[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]Vashi[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]94163407441[/TD]
[TD="align: center"]Irfan[/TD]
[TD="align: center"]Tax3[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Tax80[/TD]
[/TR]
[TR]
[TD="align: center"]02-Dec-2018,13:36:15[/TD]
[TD="align: center"]Arun[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]7259450565[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]Vashi[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]72594505651[/TD]
[TD="align: center"]Irfan[/TD]
[TD="align: center"]Tax1[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Tax14[/TD]
[/TR]
[TR]
[TD="align: center"]02-Dec-2018,13:36:15[/TD]
[TD="align: center"]Krishank[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9322508484[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]Vashi[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]93225084841[/TD]
[TD="align: center"]Sahil[/TD]
[TD="align: center"]Tax3[/TD]
[/TR]
[TR]
[TD="align: center"]02-Dec-2018,13:50:54[/TD]
[TD="align: center"]Pratik[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9920578336[/TD]
[TD="align: center"]2500[/TD]
[TD="align: center"]Vashi[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]99205783361[/TD]
[TD="align: center"]Ujuwala[/TD]
[TD="align: center"]Tax3[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] [/TD]
[TD]

Tax80​

---

---

​

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
In above table column H is Customer number(D)&Rank(G). I am not sure as how match index will work and give the output if the user only enters the phone number is sheet1 b3

Output in sheet1

[TABLE="width: 374"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Customer Number[/TD]
[TD="align: center"]High to Low[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]8975295430[/TD]
[TD="align: center"]Amount Spent[/TD]
[TD="align: center"]240[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Date Visit[/TD]
[TD="align: center"]02-Dec-2018,13:54:16[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Employee[/TD]
[TD="align: center"]Irfan[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Services[/TD]
[TD="align: center"]Tax1:- [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Tax11:- [/TD]
[/TR]
</tbody>[/TABLE]

here it will search Customer Number&high to Low (

8975295430&1) in sheet2(column H) data give all the output. Services given by a employee can be 1 or more than 1

Any idea.

​

 

[Legacy 436357]

How about like this:

Excel Workbook
	A	B	C
2	Customer Number	High to Low	Outputs
3	8975295430	Amount Spent	$300.00
4		Date Visit	02-Dec-2018,12:45:01
5		Employee	Irfan
6		Services	TAX1
Sheet1

 

[vmjan02]

hi Thanks Johnson, the match index works fine, but here the services for provided to customer number is Tax1 and Tax11

c6 output is correct, if formula applied to c7 the output of Tax11 is not coming.

if the user enter's customer number as "9416340744" then again same C6 gives the correct output as Tax3 but c7 don't give the output of Tax80.

 

[Legacy 436357]

Maybe try this instead in C6 and drag down:

=IFERROR(INDEX(Sheet2!$J$3:$J$100,MATCH($A$3,Sheet2!$D$3:$D$100,0)+ROWS(C$6:C6)-1),"")