multiple criteria return word

[jimtheeagle]

Hi folks

Here is my problems

Reference Page

Office	Current/ Non-Current	Option Type
DAL	Current	ROFO
DAL	Non-Current	ROFR
DAL	Current	Extension
WDC	Current	ROFO
WDC	Current	Expansion

<tbody>
</tbody>

Report Page

1	A	B
2	Office	ROFO/ ROFR
3	DAL	ROFO
4	WDC	WDC
5
6

<tbody>
</tbody>

Looking for a formula for B3 and B4. I tried =IF(VLOOKUP(A3,Options!A:J,3,FALSE)="ROFO","ROFO","") but only returned "ROFO" and only if it was the first Option listed for that particular site. Also didnt take into account whether the option was still current.

So basically I am looking for a formula that:
-Returns either "ROFO", "ROFR" or ""
-Only gives a result if the Option is "Current"
-Doesnt need to be the first option listed for a particular site.

Thanks in advance.

James

 

[Smitty]

Have you tried a Pivot Table?

Excel 2012
	A	B
1	Current/ Non-Current	Current
2
3	Office	Option Type
4	DAL	ROFO
5	WDC	ROFO
Sheet2

HTH,

 

[jimtheeagle]

there is actually quite a few other rows and columns that I am not showing. So I would prefer to use formulas rather than a pivot table.