Index Match in multiple coloumn

NOTIMPORTANT

New Member
Joined
Jan 16, 2017
Messages
2
Dear

If you could help me in getting the result of the data below
ABCDEFGH

<tbody>
</tbody>
NamesTypeQuantityTicket1Ticket2Ticket3Ticket4Ticket5
JamesAdult5891100005801891100005802891100005803891100005804891100005805
JamesChild4894400001801894400001802894400001803894400001804
JamesFamily3897700001746897700001747897700001748
SteffyAdult5891100006426891100006427891100006428891100006429891100006430
SteffyAdult5891100006353891100006354891100006355891100006356891100006357
MarkAdult5891100005526891100005527891100005528891100005529891100005530
MarkChild2894400001671894400001672
MarkFamily5897700001691897700001692897700001693897700001694897700001695
MarkAdult4891100006476891100006477891100006478891100006479
SidAdult5891100005776891100005777891100005778891100005779891100005780

<tbody>
</tbody>



Result:-
should search for the ticket number in the entire data and provide me with the Name, Type, Count

ABCD
TicketTypeNameCount
891100005527AdultMark2

<tbody>
</tbody>


Look forward to your favorable reply.

thanks
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Hi,

Couldn't findout what you meant with count but take a look at this and see if this helps you:
<b>Excel 2016 (Windows) 64 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Names</td><td style="font-weight: bold;;">Type</td><td style="font-weight: bold;;">Quantity</td><td style="font-weight: bold;;">Ticket1</td><td style="font-weight: bold;;">Ticket2</td><td style="font-weight: bold;;">Ticket3</td><td style="font-weight: bold;;">Ticket4</td><td style="font-weight: bold;;">Ticket5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">James</td><td style=";">Adult</td><td style="text-align: right;;">5</td><td style="text-align: right;;">891100005801</td><td style="text-align: right;;">891100005802</td><td style="text-align: right;;">891100005803</td><td style="text-align: right;;">891100005804</td><td style="text-align: right;;">891100005805</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">James</td><td style=";">Child</td><td style="text-align: right;;">4</td><td style="text-align: right;;">894400001801</td><td style="text-align: right;;">894400001802</td><td style="text-align: right;;">894400001803</td><td style="text-align: right;;">894400001804</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">James</td><td style=";">Family</td><td style="text-align: right;;">3</td><td style="text-align: right;;">897700001746</td><td style="text-align: right;;">897700001747</td><td style="text-align: right;;">897700001748</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Steffy</td><td style=";">Adult</td><td style="text-align: right;;">5</td><td style="text-align: right;;">891100006426</td><td style="text-align: right;;">891100006427</td><td style="text-align: right;;">891100006428</td><td style="text-align: right;;">891100006429</td><td style="text-align: right;;">891100006430</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Steffy</td><td style=";">Adult</td><td style="text-align: right;;">5</td><td style="text-align: right;;">891100006353</td><td style="text-align: right;;">891100006354</td><td style="text-align: right;;">891100006355</td><td style="text-align: right;;">891100006356</td><td style="text-align: right;;">891100006357</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Mark</td><td style=";">Adult</td><td style="text-align: right;;">5</td><td style="text-align: right;;">891100005526</td><td style="text-align: right;;">891100005527</td><td style="text-align: right;;">891100005528</td><td style="text-align: right;;">891100005529</td><td style="text-align: right;;">891100005530</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Mark</td><td style=";">Child</td><td style="text-align: right;;">2</td><td style="text-align: right;;">894400001671</td><td style="text-align: right;;">894400001672</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Mark</td><td style=";">Family</td><td style="text-align: right;;">5</td><td style="text-align: right;;">897700001691</td><td style="text-align: right;;">897700001692</td><td style="text-align: right;;">897700001693</td><td style="text-align: right;;">897700001694</td><td style="text-align: right;;">897700001695</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Mark</td><td style=";">Adult</td><td style="text-align: right;;">4</td><td style="text-align: right;;">891100006476</td><td style="text-align: right;;">891100006477</td><td style="text-align: right;;">891100006478</td><td style="text-align: right;;">891100006479</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Sid</td><td style=";">Adult</td><td style="text-align: right;;">5</td><td style="text-align: right;;">891100005776</td><td style="text-align: right;;">891100005777</td><td style="text-align: right;;">891100005778</td><td style="text-align: right;;">891100005779</td><td style="text-align: right;;">891100005780</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="font-weight: bold;;">Ticket</td><td style="font-weight: bold;;">Type</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Count</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">891100006353</td><td style=";">Adult</td><td style=";">Steffy</td><td style="text-align: right;text-decoration: underline;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B16</th><td style="text-align:left">{=INDIRECT(<font color="#0000FF">ADDRESS(<font color="#FF0000">MAX(<font color="#00FF00">IF(<font color="#800080">A2:H11=A16,ROW(<font color="#008080">A2:H11</font>)</font>)</font>),2</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C16</th><td style="text-align:left">{=INDIRECT(<font color="#0000FF">ADDRESS(<font color="#FF0000">MAX(<font color="#00FF00">IF(<font color="#800080">A2:H11=A16,ROW(<font color="#008080">A2:H11</font>)</font>)</font>),1</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

NOTIMPORTANT

New Member
Joined
Jan 16, 2017
Messages
2
Hi,

Couldn't findout what you meant with count but take a look at this and see if this helps you:
Excel 2016 (Windows) 64 bit
ABCDEFGH
1NamesTypeQuantityTicket1Ticket2Ticket3Ticket4Ticket5
2JamesAdult5891100005801891100005802891100005803891100005804891100005805
3JamesChild4894400001801894400001802894400001803894400001804
4JamesFamily3897700001746897700001747897700001748
5SteffyAdult5891100006426891100006427891100006428891100006429891100006430
6SteffyAdult5891100006353891100006354891100006355891100006356891100006357
7MarkAdult5891100005526891100005527891100005528891100005529891100005530
8MarkChild2894400001671894400001672891100005779
9MarkFamily5897700001691897700001692897700001693897700001694897700001695
10MarkAdult4891100006476891100006477891100006478891100006479
11SidAdult5891100005776891100005777891100005778891100005779891100005780
12
13
14
15TicketTypeNameCount
16891100006353AdultSteffy

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
B16{=INDIRECT(ADDRESS(MAX(IF(A2:H11=A16,ROW(A2:H11))),2))}
C16{=INDIRECT(ADDRESS(MAX(IF(A2:H11=A16,ROW(A2:H11))),1))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>


At first, I thanks you & am obliged to your zealous response towards my problem.
I the count is the count of the ticket

Eg:
TicketTypeNameCount
891100006353AdultSteffy1First from 5 tickets provided
891100005779AdultSid4Fourth from 5 tickets provided

<tbody>
</tbody>
-----------------------------------------------------------------------------------------------------

Situation 2

If i have a situation where the same ticket number appears to be with two people, can i get a formula for that too..

Eg:

TicketTypeNameCount
891100005779AdultSid4Fourth from 5 tickets provided
891100005779ChildMark3Third from 5 tickets provided

<tbody>
</tbody>


Look forward to your kind support.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,413
Messages
5,595,998
Members
414,037
Latest member
Roamingsmile

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
Top