Index + match

[martinpaiva]

Hi All,

I stuck with some stuff regarding the INDEX + MATCH. If you can see below, i have the following table as example with some employee Names and source of the sale. What i want to get is a list with the Employee Names, where sales were by credit card.

Employee Nm (A1)	Source (B1)
AAAAA	Debit Card
AAAAA	Credit Card
ZZZZZ	Debit Card
CCCCC	Credit Card
BBBBB	Debit Card
CCCCC	Credit Card
AAAAA	Credit Card

Right now, using the following formula.. (Paste on C2)

=SI.ERROR(INDICE($A$1:$A$5; COINCIDIR(0; INDICE(CONTAR.SI($C$1:C1; $A$1:$A$31); 0; 0); 0)); "") [spanish]
=IF.ERROR(INDEX($A$1:$A$5; MATCH(0; INDEX(COUNTIF($C$1:C1; $A$1:$A$31); 0; 0); 0)); "") [english]

I get the following... a list of unique employeeNm.

AAAAA

ZZZZZ

CCCCC

BBBBB

So, There is a way to get a list of employees but only where there sale is "Credit Card"
[highlighted in green]

Hope you have the correct answer. Thank you so much!

[AlKey]

Try this array formula in D2 and copy down

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Data Range

	A	B	C	D
1	Employee Nm (A1)	Source (B1)
2	AAAAA	Debit Card		AAAAA
3	AAAAA	Credit Card		CCCCC
4	ZZZZZ	Debit Card
5	CCCCC	Credit Card
6	BBBBB	Debit Card
7	CCCCC	Credit Card
8	AAAAA	Credit Card

[FDibbins]

Another approach using a helper column and ARRAY formula (I like AlKey's suggestion better, but I worked on this so I am posting lol)...

	A	B	C	D
1	Employee Nm (A1)	Source (B1)
2	AAAAA	Debit Card	Debit Card1	AAAAA
3	AAAAA	Credit Card	Credit Card1	CCCCC
4	ZZZZZ	Debit Card	Debit Card1
5	CCCCC	Credit Card	Credit Card1
6	BBBBB	Debit Card	Debit Card1
7	CCCCC	Credit Card	Credit Card2
8	AAAAA	Credit Card	Credit Card2

C2=B2&COUNTIFS($A$2:A2,A2,$B$2:B2,B2)
You can hide or move this if you want)
D2=IFERROR(INDEX($A:$A,SMALL(IF($C$2:$C$8="Credit Card1",ROW($A$2:$A$8)),ROWS($A$1:A1))),"")
ARRAY formula, copied down

[martinpaiva]

Alkey Works great! Thank you so much for your help and time.

[martinpaiva]

Hey FDibbins, thank you so much for the another approach. Really thanks for your time and help.

[FDibbins]

Happy to help, thanks for the feedback