if value = certain text then return value of different cell

[tijgerleks]

Hi everyone !

so i need some help with what i said in the title.

i will try the best i can to explain what i need.

i have three collums
product code, type , discount code

the 3rd collum will have a specified discount code which can be anything, for now i only have this one "FACELESSCAT10"

on a different page i want every row with the that specified discount code to give the product code but only where that discount code was used

imagine i have
A B C
product code / type / discount code /
----------------------------------------------------
2 HCMA014 / bracelet / FACELESSCAT10
3 HCMA016 / bracelet / no code used
4 HCMA021 / sunnycord / FACALESSCAT10



in a different page i want all the products that were bought with the discount code FACELESSCAT10 to be there whenever they are entered in the first page
so it would look something like this

A B
product code / type /
--------------------------------
2 HCMA014 / bracelet /
3 HCMA021 / sunnycord /

i imagine the formula would look something like this

=VLOOKUP(lookup_value, IF(lookup_value="FACELESSCAT10",range1,range2),2,0)

but i am not sure what the values would be


i hope someone can help me

thanks in advance!

 

[Scott T]

Sheet 1

	A	B	C
1	Product code	type	discount code
2	HCMA014	bracelet	FACELESSCAT10
3	HCMA016	bracelet	no code used
4	HCMA021	sunnycord	FACELESSCAT10

<tbody>
</tbody>

Sheet 2

	A	B
1	Product code	type
2	HCMA014	bracelet
3	HCMA021	sunnycord
4
5

<tbody>
</tbody>

In Sheet 2 This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will but {} around the formula. Copy across and down

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$C$2:$C$4,"FACELESSCAT10"),"",INDEX(Sheet1!A$2:A$4,SMALL(IF(Sheet1!$C$2:$C$4="FACELESSCAT10",ROW(Sheet1!$C$2:$C$4)-ROW(Sheet1!$C$2)+1),ROWS($A$2:A2))))

 

[tijgerleks]

wauw thanks a lot !
altough i have a slight problem still.

because the sheets arent called sheet 1 and 2 and arent in the same excel workbook i cant simply copy paste it
also my excel is in dutch so IF ROWS COUNTIF dont work so i need to put the correct one in dutch (thats no problem though)

i will try to manage it anyway

it would be easier if you said it like this by steps
1 = IF(ROWS select rows that contain x ) >
2 = IF(ROWS(XXX)>COUNTIF(select rows )

but that would be a hell of a hassle i imagine