if statement formula

[Ovi]

I have a problem with the following formula:

as it is returning me 0 even when 'Swap Summary'!$C:$C='Credit&Finance'!A30



=IF('Swap Summary'!$B:$B="RBS",IF('Swap Summary'!$C:$C='Credit&Finance'!A30,VLOOKUP('Credit&Finance'!A30,'Swap Summary'!$C:$E,3,FALSE),"0"),"0")

does anyone know why?

 

[Pauljj]

You are asking for the first condition, ie =IF('Swap Summary'!$B:$B="RBS", to be true before it looks at the next part IF('Swap Summary'!$C:$C='Credit&Finance'!A30

Are both conditions true ?

 

[Ovi]

yes,
if the first one is true, i want then to look into the second if statement and if the second is true i want to return the vlookup data
otherwise 0

 

[Pauljj]

The problem is that you are looking for a range, ie everthing in column B to see if it is 'RBS' . So are you simply trying to figure out whether RBS appears anywhere in column B and if it does then check if 'Credit&Finance'!A30 exists anywhere in column C ?

 

[Ovi]

yes

 

[Robert Mika]

You are referencing the whole column B and C to one cell.
Unless you are using array formula that will not work.
What would you like to achive?

 

[Ovi]

Robert as discussed with Paul i m looking for everthing in column B to see if it is 'RBS' and if RBS appears anywhere in column B , then check if 'Credit&Finance'!A30 exists anywhere in column C and if that exist returning me the related data, otherwise 0

 

[Pauljj]

Is there likely to be an occasion where for example RBS appears more then once in column B, if so what should happen ? and the same for column C

 

[Ovi]

yes RBS can be in more than one cell in column B, that s why then I am looking for a specific other data in column C

 

[Robert Mika]

=IF(COUNTIF('Swap Summary'!$B:$B,"RBS")>0,IF(COUNTIF('Swap Summary'!$C:$C,'Credit&Finance'!A30)>0,VLOOKUP('Credit&Finance'!A30,'Swap Summary'!$C:$E,3,FALSE),"0"),"0")

Try this.