Assitance with if/then type excel formula/statement

squeakums

Well-known Member
Joined
May 15, 2007
Messages
711
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)
 

Some videos you may like

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
Joined
Aug 1, 2002
Messages
54,902
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 15, 2007
Messages
711
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
Joined
Aug 1, 2002
Messages
54,902
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 15, 2007
Messages
711

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,902
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 15, 2007
Messages
711
Nevermind, I figured it out, this formula works, thanks!
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,902
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,114,362
Messages
5,547,473
Members
410,797
Latest member
mlfuson
Top