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
[TABLE="class: grid, width: 216"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Product code
[/TD]
[TD]type
[/TD]
[TD]discount code
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]HCMA014
[/TD]
[TD]bracelet
[/TD]
[TD]FACELESSCAT10
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]HCMA016
[/TD]
[TD]bracelet
[/TD]
[TD]no code used
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]HCMA021
[/TD]
[TD]sunnycord
[/TD]
[TD]FACELESSCAT10
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2

[TABLE="class: grid, width: 162"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Product code
[/TD]
[TD]type
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]HCMA014
[/TD]
[TD]bracelet
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]HCMA021
[/TD]
[TD]sunnycord
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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