# Assitance with if/then type excel formula/statement

#### squeakums

##### Well-known Member
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)

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Joe4

##### MrExcel MVP, Junior Admin
One way:
Code:
``=IF(COUNTIF(D:D,"knott")>0,"knott",[COLOR=#333333]VLOOKUP(AJ2,'TPPE Mapping'!A:D,4,FALSE))[/COLOR]``

#### squeakums

##### Well-known Member
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

##### MrExcel MVP, Junior Admin
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:
Code:
``=[COLOR=#ff0000]IF(D2="knott","knott"[/COLOR],[COLOR=#333333]VLOOKUP(AJ2,'TPPE Mapping'!A:D,4,FALSE))[/COLOR]``

#### squeakums

##### Well-known Member

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

#### Joe4

##### MrExcel MVP, Junior Admin
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

##### Well-known Member
Nevermind, I figured it out, this formula works, thanks!

Last edited:

#### Joe4

##### MrExcel MVP, Junior Admin
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.

Last edited:

Replies
2
Views
42
Replies
2
Views
39
Replies
2
Views
52
Replies
2
Views
140
Replies
0
Views
97