Setting a value in a new column for a groups of rows with an unique name (Lookup)

[Anonymous]

Hi all,

I am trying to create a lovely formula in a Column which sets the value of that column to Y Won or Y lost based on an if Function.

IF(SEARCH("Y",bid[Competitor naam],,0) && bid[Won/Lost]="Won", "Y Won", "Y Lost").

Now this is working perfectly for putting the value for Competitor Y. But lets say XYZ did a bid for a tender1 and Y wins. It will set the value for X and Z to Y Lost. And I would want them to also have the value Y wins. If have been spending a lot of time chaging this formula but everytime I dont get the right one. So I would love to know how to 'group' the tenders and then apply the values set in the IF statement. Thanks a million for your help.

Tender	Competitor	Bid Won/Lost	Wanted Result
Tender 1	X	Lost	Y won
Tender 1	Y	Won	Y won
Tender 1	Z	Lost	Y won
Tender 2	X	Won	Y lost
Tender 2	Y	Lost	Y lost
Tender 2	Z	Lost	Y lost

[johnt75 · Accepted solution]

If there can only ever be 1 winner for a tender then you could use

Who Won = 
var currentTender = 'Table'[Tender]
return LOOKUPVALUE( 'Table'[Competitor], 'Table'[Tender], currentTender, 
   'Table'[Bid won/lost], "Won") & " won"

[Anonymous]

Thanks a million... I created a new column with this formula and adjusted the above formula and referenced to the new column. Great!