Match Index

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,132
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
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.

 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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),"")
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top