Write in a cell IF a other cell is x

[Ayadin]

Hello,
I'm finishing my database, and I need for that a function that I do not know. You can have a look to the table here, to make it clearer.
My reference column is the last one. It's the one with the highest number of observation. Usually financer give money to parties, so that when you look "who gives money to candidate", you only see "parties". But actually, parties only transfer private money, that's why we need to look at the 3rd column. In 95% of cases, if in 3rd column we have #Nul, we got columns 1 = column 2. What I need is this: IF and onlyIF column 3 = #NUL, write down the valye of column 2 in a column, say, "4", if not, dont write anything (or write 0 and i'll delete 0). And after, concatenate column 4' and column 3 (original), to have a column with ALL the infos i need.
How to write such a function ?
thanks a lot ,

[shirleyxls]

I'm not sure I really understood your data as you mention Col 3 being #NUL but your example shows #NUL only in Col 4 (D).

Anyway, you can do this in two steps or just one.

To do in two steps, put this in "Formula with #NUL" (E on your sample)
=IF(d2="#NUL","",C2)

then this in the next column:
=C2&E2

You can do this in a single formula in your last column:
=IF(D2="#NUL",C2,D2)

of if you need to concatenate C and D:
=IF(D2="#NUL",C2,C2&" "&D2)
which will give a space between the two values.

As I say, I wasn't sure exactly what you expected to see, but hopefully that gives you enough to get you started.

[AliGW]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[Ayadin]

Thank you so much for your answer
indeed, you are right about the data. I deleted a column to make it clearer, and you understood properly that the columns names are now obsolete in my message.
I tried this:

but it gives me an error message on excel 2011 (mac). Why so ?

[shirleyxls]

Sorry, I don't have access to Mac so not sure why. You could try changing d2 to D2 - I don't know how sensitive Mac Excel is - Windows just changes references to capital letters when you press enter.

[FDibbins]

Ayadin if you have x-posted on another forum (as Ali suggests, you are required to post a link to that post, pleasd

[Ayadin]

Indeed i did a mess, i thought it was the same forum : http://www.mrexcel.com/forum/excel-q...3D-if-not.html

This is what I did finally and it worked:

=IF(d2="#NUL"; ""; C2)

thank you all

[shirleyxls]

Ah yes, I forget that in the European settings you have to use a semi-colon ; instead of a comma , in formulas