Nested IF driving me nuts...

[John Croson]

I have a workbook with a few sheets in it.

One has master data:
Status Item # Description
Film Former Pkg Lot Code format
A 00121 My item
00131 - XYZXYZ

Many lines of data. I have another sheet that users are instructed to
enter the item number ( In Cell B1), and it populates a cell below
with VLOOKUP data. There are a number of cells that do this.

This formula displays the Description.

=IF($B1<>"",VLOOKUP($B1,MasterInfo!$C$3:$D$228,2,FALSE),"")

The conundrum is the Status field. What we'd like to do is:
1. Check if a number entered in B1 matches one in the MasterInfo
sheet.
2. If it does, display the description IF the Status is A. If the
status is I, display "Inactive Profile". If the Status is P, display
"Pending Profile".

Anyone that could assist me is greatly appreciated.

 

[Pete_UK]

Try this:

=IF($B1<>"",IF(ISNA(MATCH($B1,MasterInfo!$C:$C,0)),"",IF(INDEX
(MasterInfo!$A:$A,MATCH($B1,MasterInfo!$C:$C,0))="I","Inactive
Profile",IF(INDEX(MasterInfo!$A:$A,MATCH($B1,MasterInfo!$C:$C,0))
="P","Pending Profile",VLOOKUP($B1,MasterInfo!$C$3:$D$228,2,0)))))

It doesn't test specifically for a status of A - I've assumed that the
status can only be I, P or A.

Hope this helps.

Pete

 

[John Croson]

Try this:

=IF($B1<>"",IF(ISNA(MATCH($B1,MasterInfo!$C:$C,0)),"",IF(INDEX
(MasterInfo!$A:$A,MATCH($B1,MasterInfo!$C:$C,0))="I","Inactive
Profile",IF(INDEX(MasterInfo!$A:$A,MATCH($B1,MasterInfo!$C:$C,0))
="P","Pending Profile",VLOOKUP($B1,MasterInfo!$C$3:$D$228,2,0)))))

It doesn't test specifically for a status of A - I've assumed that the
status can only be I, P or A.

Hope this helps.

Pete

Thanks for trying this, but it still only prints the description,
regardless of the status. I'll try mucking about with it today.

 

[Pete_UK]

Just check that you do not have any space characters in there along
with the status letter. Use =LEN( ) to find out how many characters
you have in the status cells.

Hope this helps.

Pete

 

[John Croson]

Just check that you do not have any space characters in there along
with the status letter. Use =LEN( ) to find out how many characters
you have in the status cells.

Hope this helps.

Pete

Thanks, but no change. LEN = 1.

Any other ideas?

 

[John Croson]

Just check that you do not have any space characters in there along
with the status letter. Use =LEN( ) to find out how many characters
you have in the status cells.

Hope this helps.

Pete

IT WORKED. I just screwed something up prior to my last post.

THANKS FOR THE HELP!!!!!!!

 

[Pete_UK]

You're welcome, John - thanks for feeding back (6 weeks later !!)

Pete