Still could use help on a tough VLOOKUP
 

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(IS NA(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.

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 1
=IF(A4="","",IF(AND(NOT(ISNA(VLOOKUP(A4,CSM,9,FALS E))),VLOOKUP(A4,CSM,9,FALSE)="X"),"X",IF(AND(NOT(I SNA(VLOOKUP(A4,NPM,9,FALSE))),VLOOKUP(A4,NPM,9,FAL SE)="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.


"KenRamoska" wrote:

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(IS NA(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.