Assitance with if/then type excel formula/statement

[squeakums]

First, I want the formula to search column D, if column D is = "knott", then formula will make current cell "knott", otherwise use the formula below
=VLOOKUP(AJ2,'TPPE Mapping'!A:D,4,FALSE)

 

[Joe4]

One way:

=IF(COUNTIF(D:D,"knott")>0,"knott",[COLOR=#333333]VLOOKUP(AJ2,'TPPE Mapping'!A:D,4,FALSE))[/COLOR]

 

[squeakums]

It's almost right, although I don't want it looking in the entire column D:D, only D2, to see if "knott", if not use vlookup.

 

[Joe4]

It's almost right, although I don't want it looking in the entire column D:D, only D2, to see if "knott", if not use vlookup.

That's much easier:

=[COLOR=#ff0000]IF(D2="knott","knott"[/COLOR],[COLOR=#333333]VLOOKUP(AJ2,'TPPE Mapping'!A:D,4,FALSE))[/COLOR]

 

[squeakums]

Causes an #n/a error because it isn't going to the 2nd condition. Maybe an iferror statement?

 

[Joe4]

If you are getting a #N/A error, that means that:
- D2 is NOT equal to "knott"
AND
- it cannot find the value from cell AJ2 in column A of your 'TPPE Mapping' sheet.

So, none of your conditions are being met.
What do you want it to return when that happens?

 

[squeakums]

Nevermind, I figured it out, this formula works, thanks!

 

[Joe4]

Not true, because it is filling in knott if it finds knott, but it isn't doing the vlookup if knott isn't there

Ah, but it is!

I think you are not quite understanding how VLOOKUPs work. If it gets to the VLOOKUP part, and it cannot find the value you are trying to look up, it returns the #N/A error.
That is what appears is happening here.

What is the exact value you have in cell AP2?
It is telling you that it cannot find that value in column A of your 'TPPE Mapping' sheet.