Index Match in multiple coloumn

[NOTIMPORTANT]

Dear

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

A

B

C

D

E

F

G

H

<tbody>
</tbody>

Names	Type	Quantity	Ticket1	Ticket2	Ticket3	Ticket4	Ticket5
James	Adult	5	891100005801	891100005802	891100005803	891100005804	891100005805
James	Child	4	894400001801	894400001802	894400001803	894400001804
James	Family	3	897700001746	897700001747	897700001748
Steffy	Adult	5	891100006426	891100006427	891100006428	891100006429	891100006430
Steffy	Adult	5	891100006353	891100006354	891100006355	891100006356	891100006357
Mark	Adult	5	891100005526	891100005527	891100005528	891100005529	891100005530
Mark	Child	2	894400001671	894400001672
Mark	Family	5	897700001691	897700001692	897700001693	897700001694	897700001695
Mark	Adult	4	891100006476	891100006477	891100006478	891100006479
Sid	Adult	5	891100005776	891100005777	891100005778	891100005779	891100005780

<tbody>
</tbody>

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

A	B	C	D
Ticket	Type	Name	Count
891100005527	Adult	Mark	2

<tbody>
</tbody>

Look forward to your favorable reply.

thanks

 

[jorismoerings]

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
	A	B	C	D	E	F	G	H
1	Names	Type	Quantity	Ticket1	Ticket2	Ticket3	Ticket4	Ticket5
2	James	Adult	5	891100005801	891100005802	891100005803	891100005804	891100005805
3	James	Child	4	894400001801	894400001802	894400001803	894400001804
4	James	Family	3	897700001746	897700001747	897700001748
5	Steffy	Adult	5	891100006426	891100006427	891100006428	891100006429	891100006430
6	Steffy	Adult	5	891100006353	891100006354	891100006355	891100006356	891100006357
7	Mark	Adult	5	891100005526	891100005527	891100005528	891100005529	891100005530
8	Mark	Child	2	894400001671	894400001672
9	Mark	Family	5	897700001691	897700001692	897700001693	897700001694	897700001695
10	Mark	Adult	4	891100006476	891100006477	891100006478	891100006479
11	Sid	Adult	5	891100005776	891100005777	891100005778	891100005779	891100005780
12
13
14
15	Ticket	Type	Name	Count
16	891100006353	Adult	Steffy
Sheet1

Cell Formulas
Range	Formula
B16	{=INDIRECT(ADDRESS(MAX(IF(A2:H11=A16,ROW(A2:H11))),2))}
C16	{=INDIRECT(ADDRESS(MAX(IF(A2:H11=A16,ROW(A2:H11))),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[NOTIMPORTANT]

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

	A	B	C	D	E	F	G	H
1	Names	Type	Quantity	Ticket1	Ticket2	Ticket3	Ticket4	Ticket5
2	James	Adult	5	891100005801	891100005802	891100005803	891100005804	891100005805
3	James	Child	4	894400001801	894400001802	894400001803	894400001804
4	James	Family	3	897700001746	897700001747	897700001748
5	Steffy	Adult	5	891100006426	891100006427	891100006428	891100006429	891100006430
6	Steffy	Adult	5	891100006353	891100006354	891100006355	891100006356	891100006357
7	Mark	Adult	5	891100005526	891100005527	891100005528	891100005529	891100005530
8	Mark	Child	2	894400001671	894400001672	891100005779
9	Mark	Family	5	897700001691	897700001692	897700001693	897700001694	897700001695
10	Mark	Adult	4	891100006476	891100006477	891100006478	891100006479
11	Sid	Adult	5	891100005776	891100005777	891100005778	891100005779	891100005780
12
13
14
15	Ticket	Type	Name	Count
16	891100006353	Adult	Steffy

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula 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:

Ticket	Type	Name	Count
891100006353	Adult	Steffy	1	First from 5 tickets provided
891100005779	Adult	Sid	4	Fourth 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:

Ticket	Type	Name	Count
891100005779	Adult	Sid	4	Fourth from 5 tickets provided
891100005779	Child	Mark	3	Third from 5 tickets provided

<tbody>
</tbody>

Look forward to your kind support.

Thanks