Nested If Function - best option

[Destiny85]

Hi All,

I am trying to write an if function for the below.

[TABLE="width: 431"]
<TBODY>[TR]
[TD]Option 1</SPAN>[/TD]
[TD]Option 2</SPAN>[/TD]
[TD]Option 3</SPAN>[/TD]
[TD]Option 4</SPAN>[/TD]
[TD]Option Cost</SPAN>[/TD]
[/TR]
[TR]
[TD]Y</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD] =IF(A2="Y",'Costs '!D24,"")</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]Y</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL span=4><COL></COLGROUP>[/TABLE]



Currently I can get it to work for one option using =IF(AP3="Y",'Costs '!D24,"") but I want to carry this formula across the next 3 columns.


I am guessing I need an OR function or something inbetweeen but am stuck.

There will only be one Y on each line so I want to be able to write a formula that will pick up the Y and give me the cost from a separate table. Not sure if a VLookup can be used this way so i went with the IF function.

Any help will be appreciated.

 

[JoeMo]

How about:
=IF(COUNTIF(A2:D2,"Y")=1,'Costs'!D24,"")

EDIT: After reading your post again I don't think this is what you want. Can you post the desired result(s) if the Y is under each of the other three options?