Help on logical argument w/ Vlookup

[MrSqueal]

G'day,

I'm having trouble with a logical argument using Vlookup where it is incorrectly resolving some of the arguments:

=IF(VLOOKUP(A97,'ME MASTER MWP'!$B$12:$F$306,5)="NEW","NEW",IF(VLOOKUP(A97,'ME MASTER MWP'!$B$12:$F$306,5)="PROG","PROG",IF(VLOOKUP(A97,'ME MASTER MWP'!$B$12:$F$306,5)="COMP","COMP",IF(VLOOKUP(A97,'ME MASTER MWP'!$B$12:$F$306,5)="MCP","MCP",IF(VLOOKUP(A97,'WEE MASTER MWP'!$B$12:$F$143,5)="NEW","NEW",IF(VLOOKUP(A97,'WEE MASTER MWP'!$B$12:$F$143,5)="PROG","PROG",IF(VLOOKUP(A97,'WEE MASTER MWP'!$B$12:$F$143,5)="COMP","COMP",IF(VLOOKUP(A97,'WEE MASTER MWP'!$B$12:$F$143,5)="MCP","MCP",IF(VLOOKUP(A97,'EXEC MASTER MWP'!$B$12:$F$28,5)="NEW","NEW",IF(VLOOKUP(A97,'EXEC MASTER MWP'!$B$12:$F$28,5)="PROG","PROG",IF(VLOOKUP(A97,'EXEC MASTER MWP'!$B$12:$F$28,5)="COMP","COMP",IF(VLOOKUP(A97,'EXEC MASTER MWP'!$B$12:$F$28,5)="MCP","MCP","NULL"))))))))))))

The intent of this formula is that it is checking 3 separate sheets for the individual item in cell "A91" and its state either "NEW","PROG","COMP" or "MCP" and setting the cell of this worksheet to be the same. Unfortunately, it is somehow resolving an item that is set to "COMP" on worksheet "ME MASTER MWP" to "MCP" - It seems to be working in some instances but not in others. For the purposes of this worksheet I really only want to know if the item which is listed on one of the 3 other sheets is "MCP" or not. I have tried a simpler variation but it was resolving as #N/A. At first I thought this was working until I started to interrogate the data and realised it was giving the incorrect result.

Any ideas?

Thanks in advance.

Ryan

 

[MrSqueal]

Ok, so I tried adding the optional exact match option i.e.

=IF(VLOOKUP(A91,'ME MASTER MWP'!$B$12:$F$306,5,FALSE)="NEW","NEW",IF(VLOOKUP(A91,'ME MASTER MWP'!$B$12:$F$306,5,FALSE)="PROG","PROG",IF(VLOOKUP(A91,'ME MASTER MWP'!$B$12:$F$306,5,FALSE)="COMP","COMP",IF(VLOOKUP(A91,'ME MASTER MWP'!$B$12:$F$306,5,FALSE)="MCP","MCP",IF(VLOOKUP(A91,'WEE MASTER MWP'!$B$12:$F$143,5,FALSE)="NEW","NEW",IF(VLOOKUP(A91,'WEE MASTER MWP'!$B$12:$F$143,5,FALSE)="PROG","PROG",IF(VLOOKUP(A91,'WEE MASTER MWP'!$B$12:$F$143,5,FALSE)="COMP","COMP",IF(VLOOKUP(A91,'WEE MASTER MWP'!$B$12:$F$143,5,FALSE)="MCP","MCP",IF(VLOOKUP(A91,'EXEC MASTER MWP'!$B$12:$F$28,5,FALSE)="NEW","NEW",IF(VLOOKUP(A91,'EXEC MASTER MWP'!$B$12:$F$28,5,FALSE)="PROG","PROG",IF(VLOOKUP(A91,'EXEC MASTER MWP'!$B$12:$F$28,5,FALSE)="COMP","COMP",IF(VLOOKUP(A91,'EXEC MASTER MWP'!$B$12:$F$28,5,FALSE)="MCP","MCP","NULL"))))))))))))

This resulted in the data which is all present on the first sheet "ME MASTER MWP" resolving correctly but then all the data from the second two sheets "WEE MASTER MWP" and "EXEC MASTER MWP" resolves as #N/A.

 

[BigBeachBananas]

That's quite a monstrous formula. I recommend you share a sample of your workbook using xl2bb or Dropbox. There should be a more optimal solution.

 

[MrSqueal]

I think I was over complicating the issue since I knew which items featured on each sheet I have just set the logical argument for that item to check only the appropriate sheet and its all working fine now. I would have preferred an option that did the leg work for me from the start but this works all the same.

 

[StephenCrump]

@MrSqueal, welcome to the Forum!

Ok, so I tried adding the optional exact match option ...

VLOOKUP behaves very differently with TRUE and FALSE arguments. The argument is "optional" only in the sense that you don't need to specify it if you want TRUE, as TRUE is the default. If you want FALSE (as it looks like you do here), you need to specify FALSE, or leave blank, e.g.

=VLOOKUP("abc",A1:B10,2,FALSE)
=VLOOKUP("abc",A1:B10,2,)

Your basic problem here is that you've got a structure like this:

=IF(VLOOKUP()=Something,TrueValue,ElseFalseValue)

The problem is that if VLOOKUP doesn't find a match and returns #N/A, then the formula will return #N/A, rather than ElseFalseValue.

Extrapolating my simple example to two sheets, you could do something like this:

=IFERROR(VLOOKUP("abc",Sheet1!A1:B10,2,),IFERROR(VLOOKUP("abc",Sheet2!A1:B10,2,),"Not found on Sheet1 or Sheet2"))

It sounds as if the only options are "NEW","PROG","COMP", "MCP" or not there at all? If so, you don't need to test for each one, you can simply adapt the above formula.