Anyone able to debug my formula? Nested VLOOKUPs, IF statements & TEXT functions

[OpaquePineapple]

Hey,

I'm trying to use the below formula to build a naming convention:

=IF(A8="Events",D8&"-"&PROPER(E8)&"-["&G8&"]"&"-{"&(IF(VLOOKUP(E8,'Keyword Ideas'!$A:$E,5,0)<>""),(TEXT(VLOOKUP(E8,'Keyword Ideas'!$A:$E,5,0)),"yyyy-mm-dd"),"")&"}",D8&"-"&PROPER(E8)&"-["&G8&"]")

And am getting the error "There's a problem with this formula". When throwing the error, the "" (in red) ends up highlighted, but I'm not sure what the issue is. Anyone able to help?

Much appreciated!

[Olly]

TryL

Please Login or Register  to view this content.

[dosydos]

Please Login or Register  to view this content.

[jason.b75]

We would be able to help you better if you explain what you're trying to do rather than just posting a broken formula.

Maybe

Please Login or Register  to view this content.

[OpaquePineapple]

Olly & dosydos have nailed it - thanks guys. For future reference, was the issue just that I was too liberal with brackets and a few needed removing?

Thanks again!

[Olly]

Yes, there were brackets in the wrong places - I also restructured the order of your formula, to make it a little more efficient ( you didn't need to repeat ( =D8&"-"&PROPER(E8)&"-["&G8&"]" ) twice.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You may also 'Add Reputation' to those who helped you. Thanks.

[jason.b75]

I also restructured the order of your formula, to make it a little more efficient ( you didn't need to repeat ( =D8&"-"&PROPER(E8)&"-["&G8&"]" ) twice.

I missed that bit, Olly. But you missed the second vlookup that wasn't needed

edit:- combining both efforts

Please Login or Register  to view this content.

[Olly]

I missed that bit, Olly. But you missed the second vlookup that wasn't needed

I resisted the temptation to rewrite as the more efficient INDEX / MATCH...

[OpaquePineapple]

I resisted the temptation to rewrite as the more efficient INDEX / MATCH...

Feel free

Thanks again guys

[OpaquePineapple]

I missed that bit, Olly. But you missed the second vlookup that wasn't needed

edit:- combining both efforts

Please Login or Register  to view this content.

This one doesn't quite work actually - we need the control for

IF(VLOOKUP(E8,'Keyword Ideas'!$A:$E,5,0)<>""

otherwise the date gets returned as 1900-01-00. But thank you - not necessarily clear without the full use case in front of you.

[jason.b75]

Sorry, I had some serious brianfart going on earlier

It should have eliminated the 1900-01-00 dates when the result of the vlookup was empty, but i put the &"" in the wrong place. Is should have been VLOOKUP(E8,A:E,5,0)&"" not after E8 as I did earlier.

I also had A8="Events" in the wrong place.

Correcting both of the above and rewriting with INDEX / MATCH, hopefully with no more silly mistakes

Please Login or Register  to view this content.

I've also added an error trap, just in case E8 is not found in the list of keyword ideas.