Still could use help on a tough VLOOKUP

[KenRamoska]

Hi,
I have 4 sheets in my workbook.
On sheet one is the summation stuff.
On sheets 2-4 is the same core class plus one original class.
I would like to know how to write the VLOOKUP so that it looks at
sheets 2-4 and puts it on sheet one. I can get the first column to work but
I have 4 columns to do. And an IF VLOOKUP ISNA combo puts an x in all 4
columns even when just one x is put on for one person.
Name....core1 core2 core3 core4 is on sheet 2-4.
On sheet one just the name and the cores.
I want it to find the name somewhere on one of those 3 sheets and put the x
for each core class as they take it. If they take the first....the x pops in
all 4 which is bad.
Here is the last formula I tried with help from wonderful people here.

IF(A4="","",IF(ISNA(VLOOKUP(A4,CSM,9,FALSE)),IF(ISNA(VLOOKUP(A4,NPM,9,FALSE)),IF(ISNA(VLOOKUP(A4,PM,9,FALSE)),"N","Z"),"Y"),"X"))

This puts the x , y, or z in the first ok but then no matter what repeats it
in the other 3 even if they have not taken them.
CSM is a range on sheet 2 NSM is a range on sheet 3 and so on

Anyone know what can help me? Thanks sorry its logn.

 

[bj]

The current equation just looks to see if the lookup name is there,
It sounds as though you need an additional criteria to see if a given cell
has an entry
I dont know what you need but if your columns in sheet2 are say 9,10,11 and
12 for the four cores
what you might do is try for core
=IF(A4="","",IF(AND(NOT(ISNA(VLOOKUP(A4,CSM,9,FALSE))),VLOOKUP(A4,CSM,9,FALSE)="X"),"X",IF(AND(NOT(ISNA(VLOOKUP(A4,NPM,9,FALSE))),VLOOKUP(A4,NPM,9,FALSE)="Y"),"Y",IF(AND(NOT(ISNA(VLOOKUP(A4,PM,9,FALSE))),VLOOKUP(A4,PM,9,FALSE)="Z"),"Z","")))

for core 2,3,and 4 change the 9s to 10s, 11s and 12s in the vlookups.