Is there a way to have use v lookup or a different formula with duplicate lookup but different row v-CodePudding

I have two worksheets. The first worksheet has all the data that I am working with (the first table shown). The second worksheet contains information I want to have pulled from the first worksheet (table 3 shown).

I am currently given the "outcome" in worksheet two and I want to pull the type and cost from worksheet one. Since there are duplicates in my outcome, the vlookup I use only gives the first row that contains the outcome. Table 2 shows how it looks with the vlookup. I would like it to look like table 3, with the same outcome but with both the type listed and the cost as well.

Thank you for your help.

Table 1

Outcome	Type	Cost
a	labor	45
b	material	23
b	labor	43
c	material	78
c	labor	98
d	material	12
e	material	45
e	labor	78

Table 2

Outcome	Type	Cost
a	labor	45
c	material	78
c	material	78
d	material	12
e	material	45
e	material	45

Table3

Outcome	Type	Cost
a	labor	45
c	material	78
c	labor	98
d	material	12
e	material	45
e	labor	78

CodePudding user response：

array formula：

=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$1:$A$100=$A2,ROW($1:$100)),COUNTIF($A$2:$A2,$A2))),"")