=if

[scouser]

I have a formula as follows:

=IF(D2="Y","T",C2)

So if column D has a Y then set the value of column E to T else make it the same as the value in column C. However the function simply returns the values from column C in column E even when column D has a Y?

Any ideas?
Thanks,
Phil.

 

[pbaldy]

I just tested that and it worked as expected. Are you sure D doesn't have a space or anything in addition to the Y?

 

[scouser]

Hi Paul, you were right. I changed to " Y" and it worked OK.

How are you on VLOOKUP? I have 2 excel files.

File 1: Products - 35000 rows
File 2: Structures - 5000 rows

File 1 has a product Product Type in column C of either M / P or T

However if ProductId exists in column A in File 2 matches ProductId in column A in File 1 then set Product Type to B in Column C of File 1

Does that make sense?
Thanks,
Phil.

 

[pbaldy]

I first started programming in Lotus 123, before switching to Excel and then Access. I'm not great with VLOOKUP, but I've got some skills. This is in a worksheet to find matching values in another file, or zero if not found in the other. Does that help?

=IF(ISERROR(VLOOKUP(T421,'W:\401k\Match\12-06 Match\[2006 all 401k only.xls]401K Match Report '!$T$8:$AD$417,11,FALSE)),0,VLOOKUP(T421,'W:\401k\Match\12-06 Match\[2006 all 401k only.xls]401K Match Report '!$T$8:$AD$417,11,FALSE))

And I'm not responsible for the funky file/sheet names! People give me their files and ask me to add formulas to them.

 

[scouser]

I came up with:

=VLOOKUP(A2,STRUCTURESP5.xlsx!$1:$1048576,3,FALSE)

So the column being compared in both files is A (ProductId). If a value in file 1column A exists within column A of File B then return the value in column 3 of file 2 (5)

Does that sound reasonable. It is critical I do not omit data or make a mistake.
Thanks,
Phil.

 

[pbaldy]

That looks reasonable. Mine was complicated by the fact that if the lookup value didn't exist in the second file, the VLOOKUP returns NA, which screws up any math calculations based on that column. That's why I had the IF/ISERROR in there. Mine's from a file doing payroll calculations, so it's certainly accurate. It's best if you can compare the result to something to double-check it though.

 

[qafself]

=if(isna(VLOOKUP(A2,STRUCTURESP5.xlsx!$1:$1048576,3,FALSE)),"",VLOOKUP(A2,STRUCTURESP5.xlsx!$1:$1048576,3,FALSE))

Will trap errors if match not found but you don't seem to have a column reference in the formula e.g. $1:$1048576

 

[scouser]

Thanks guys.
Phil.