Please help on formula that will not work (If-And formula)
 

Excel 2007. I cannot seem to get this formula to work:

=IF(AND((VLOOKUP(CONCATENATE(TRIM($A29),TRIM($A$27 )),'test
(083)'!$J:$L,3,FALSE)=C$28),(VLOOKUP(CONCATENATE( TRIM(A29),TRIM($C$27)),Overrides!$E:$F,2,FALSE)<" N")),"Y"," ")

I am trying to do an if statement to enter a "Y" or blank based on my AND
parameter. The first AND logical test is checking to see if the vlookup
number is greater than the number in cell C28 which is a number 4. This part
of the formula seems to work as I tested it by itself. It is the second
logical test that doesn't seem to work. I only want to enter a "Y" in the
cell if my vlookup number is greater than a 4, which it is ,and also if the
cell matching the vlookup is equal to a "N". If so enter a Y, and if not
enter a blank. The cell that matches has a "N" in it so a Y should be
entered based on my formula. I just one blank cell (the first one) and the
rest are cells with "#N/A" based on cells with my formula.

Please help on formula that will not work (If-And formula)
 

In the second VLOOKUP, you are using TRIM($C$27) and I think you meant
TRIM($A$27) just as in the first VLOOKUP formula. I could be totally off
base.


"duketter" wrote:

Excel 2007. I cannot seem to get this formula to work:

=IF(AND((VLOOKUP(CONCATENATE(TRIM($A29),TRIM($A$27 )),'test
(083)'!$J:$L,3,FALSE)=C$28),(VLOOKUP(CONCATENATE( TRIM(A29),TRIM($C$27)),Overrides!$E:$F,2,FALSE)<" N")),"Y"," ")

I am trying to do an if statement to enter a "Y" or blank based on my AND
parameter. The first AND logical test is checking to see if the vlookup
number is greater than the number in cell C28 which is a number 4. This part
of the formula seems to work as I tested it by itself. It is the second
logical test that doesn't seem to work. I only want to enter a "Y" in the
cell if my vlookup number is greater than a 4, which it is ,and also if the
cell matching the vlookup is equal to a "N". If so enter a Y, and if not
enter a blank. The cell that matches has a "N" in it so a Y should be
entered based on my formula. I just one blank cell (the first one) and the
rest are cells with "#N/A" based on cells with my formula.

Please help on formula that will not work (If-And formula)
 

check the location of your )
I think that the one right after false in 'test(o83)'!$j$l,3,false)
is closing your vlookup prematurely

Hope this is it - good luck


"JLatham" wrote:

In the second VLOOKUP, you are using TRIM($C$27) and I think you meant
TRIM($A$27) just as in the first VLOOKUP formula. I could be totally off
base.


"duketter" wrote:

Excel 2007. I cannot seem to get this formula to work:

=IF(AND((VLOOKUP(CONCATENATE(TRIM($A29),TRIM($A$27 )),'test
(083)'!$J:$L,3,FALSE)=C$28),(VLOOKUP(CONCATENATE( TRIM(A29),TRIM($C$27)),Overrides!$E:$F,2,FALSE)<" N")),"Y"," ")

I am trying to do an if statement to enter a "Y" or blank based on my AND
parameter. The first AND logical test is checking to see if the vlookup
number is greater than the number in cell C28 which is a number 4. This part
of the formula seems to work as I tested it by itself. It is the second
logical test that doesn't seem to work. I only want to enter a "Y" in the
cell if my vlookup number is greater than a 4, which it is ,and also if the
cell matching the vlookup is equal to a "N". If so enter a Y, and if not
enter a blank. The cell that matches has a "N" in it so a Y should be
entered based on my formula. I just one blank cell (the first one) and the
rest are cells with "#N/A" based on cells with my formula.

Please help on formula that will not work (If-And formula)
 

Thanks for the reply but I did mean to use $C$27. It seems like the AND part
of my statement won't work. In doing some more looking it appears maybe I am
using the wrong statement? What I want is if the first vlookup is greater
than or equal to C28 and if the second vlookup is not equal to a N enter a Y.
However sometimes there is not any data for that individual in the Override
table (2nd vlookup) so if it is an N or there is not even a match for that
individual in the 2nd vlookup table it should return a Y. Is that where I am
getting it confused? Will it return a blank when there is not a match for
the 2nd vlookup? If there is not a match or it does not equal N then I want
it to return a Y in the cell.

Thanks!!!!

"duketter" wrote:

Excel 2007. I cannot seem to get this formula to work:

=IF(AND((VLOOKUP(CONCATENATE(TRIM($A29),TRIM($A$27 )),'test
(083)'!$J:$L,3,FALSE)=C$28),(VLOOKUP(CONCATENATE( TRIM(A29),TRIM($C$27)),Overrides!$E:$F,2,FALSE)<" N")),"Y"," ")

I am trying to do an if statement to enter a "Y" or blank based on my AND
parameter. The first AND logical test is checking to see if the vlookup
number is greater than the number in cell C28 which is a number 4. This part
of the formula seems to work as I tested it by itself. It is the second
logical test that doesn't seem to work. I only want to enter a "Y" in the
cell if my vlookup number is greater than a 4, which it is ,and also if the
cell matching the vlookup is equal to a "N". If so enter a Y, and if not
enter a blank. The cell that matches has a "N" in it so a Y should be
entered based on my formula. I just one blank cell (the first one) and the
rest are cells with "#N/A" based on cells with my formula.