Issue with IF and VLookup Formula

[hammerhead13]

What I am trying to accomplish is for it to check to see if the Cell Starts with "4300" to Return the Data. If it does not then I want to Add the Prefix "4300" to the Cell. I can't see what I have Wrong. I need another set of more experienced Eyes. Thank you in advance!

=IF(LEFT(VLOOKUP($B$8,'Data Dump'!$B$2:$N$6000,10,FALSE),4))="4300", VLOOKUP($B$8,'Data Dump'!$B$2:$N$6000,10,FALSE),4), &"4300" &VLOOKUP($B$8,'Data Dump'!$B$2:$N$6000,10,FALSE),4)

 

[Fluff]

Try

=IF(LEFT(VLOOKUP($B$8,'Data dump'!$B$2:$N$6000,10,FALSE),4)="4300", VLOOKUP($B$8,'Data dump'!$B$2:$N$6000,10,FALSE), "4300" &VLOOKUP($B$8,'Data dump'!$B$2:$N$6000,10,FALSE))

 

[Special-K99]

Why dont you shorten the formula like this?

=IF(LEFT(VLOOKUP($B$8,'Data Dump'!$B$2:$N$6000,10,FALSE),4)="4300", "","4300")&VLOOKUP($B$8,'Data Dump'!$B$2:$N$6000,10,FALSE)

So regardless of what the VLOOKUP result is you either concatenate a null or 4300 to the start.

 

[JimM]

Try

=IF(LEFT(VLOOKUP($B$8,'Data Dump'!$B$2:$N$6000,10,FALSE),4)="4300", VLOOKUP($B$8,'Data Dump'!$B$2:$N$6000,10,FALSE), "4300" &VLOOKUP($B$8,'Data Dump'!$B$2:$N$6000,10,FALSE))

 

[Fluff]

If the 4300 can only exist as the first 4 characters you can use

="4300"&SUBSTITUTE(VLOOKUP($B$8,'Data dump'!$B$2:$N$6000,10,FALSE),"4300","")

 

[hammerhead13]

Try

=IF(LEFT(VLOOKUP($B$8,'Data dump'!$B$2:$N$6000,10,FALSE),4)="4300", VLOOKUP($B$8,'Data dump'!$B$2:$N$6000,10,FALSE), "4300" &VLOOKUP($B$8,'Data dump'!$B$2:$N$6000,10,FALSE))

I see where I messed up. I forgot I had ",4)" on my last 2 Vlookups which I needed to Remove. that's the problem with Copy and Pasting and not paying closer attention. Thank you very much!


Thank you everyone for your assistance!!!

 

[Fluff]

Glad we could help & thanks for the feedback.