IF AND MATCH data type issue
 

This formula is working fine, except that my data is imported from MS Access
and Excel seems not to recognize the correct data type.
I have two data sets imported from two different Access queries on the same
worksheet. I'm using my first data set/range as the base, then I have added 4
new columns that I need to populate with "Y"/"N" by looking up 2 to 3 vlaues
on my base to match them with values on the 2nd data range. So far my formula
give the correct results for some rows while giving incorrect results for
others. I have foreced the data type to match on both ranges. I also cleared
both ranges, applied my desired data type before importing the data, but
still that didn't help. I also tried to use the "--" (I use it sometimes in
SUMPRODUCT), it will change the incorrect results to the correct ones, but it
will do the same to the correct ones (change them to show incorrect results).
If I type the values over in one row I will get the correct results, but
obviously that not an option for thousands of records, and having to do that
every time we refresh the data. Is there a better way to force the formula to
ignore any data mismatch? All of my data in both ranges are either 'general'
or 'text'.

Thanks.

=IF(AND(ISNUMBER(MATCH(Z13,AC2:AC4500,0)),ISNUMBER (MATCH(AA13,AF2:AF4500,0)),ISNUMBER(MATCH(AB13,AG2 :AG4500,0))),"Y","N")

--

IF AND MATCH data type issue
 

Hi,

It would help if you show us a small amount of data and what you are getting
as the result, and what you expect.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"EZ" wrote:

This formula is working fine, except that my data is imported from MS Access
and Excel seems not to recognize the correct data type.
I have two data sets imported from two different Access queries on the same
worksheet. I'm using my first data set/range as the base, then I have added 4
new columns that I need to populate with "Y"/"N" by looking up 2 to 3 vlaues
on my base to match them with values on the 2nd data range. So far my formula
give the correct results for some rows while giving incorrect results for
others. I have foreced the data type to match on both ranges. I also cleared
both ranges, applied my desired data type before importing the data, but
still that didn't help. I also tried to use the "--" (I use it sometimes in
SUMPRODUCT), it will change the incorrect results to the correct ones, but it
will do the same to the correct ones (change them to show incorrect results).
If I type the values over in one row I will get the correct results, but
obviously that not an option for thousands of records, and having to do that
every time we refresh the data. Is there a better way to force the formula to
ignore any data mismatch? All of my data in both ranges are either 'general'
or 'text'.

Thanks.

=IF(AND(ISNUMBER(MATCH(Z13,AC2:AC4500,0)),ISNUMBER (MATCH(AA13,AF2:AF4500,0)),ISNUMBER(MATCH(AB13,AG2 :AG4500,0))),"Y","N")

--

IF AND MATCH data type issue
 

This is my base data range with the formula on 'Prod_Item' field... obviously
there are many other columns, but this is as a sample.

Dest_Loc Prod_CD Prod_Item?
36370 002003006 Y
36370 002003071 Y
31350 003003071 Y
36370 003012071 Y
38370 003012071 Y
36370 003025071 Y
34390 005015064 Y
34390 005015061 Y
36370 005015061 Y
31310 006015030 Y
31310 006015284 Y

#################

This is my lookup range:

DIVLOC Class MFG_CLS Prod_Cd SIZE_Cd Flvr_Cd
36370 002 002 002003009 003 009
34390 002 002 002003011 003 011
34390 002 002 002003045 003 045
34390 002 002 002008011 008 011
34390 002 002 002009009 009 009
34390 002 002 002009011 009 011
31310 002 002 002009039 009 039
34390 002 002 002009045 009 045
34390 002 002 002010009 010 009
34390 002 002 002010011 010 011
34390 002 002 002010041 010 041
34390 002 002 002012009 012 009
34390 002 002 002012011 012 011
34390 002 002 002012014 012 014
34390 002 002 002012041 012 041
34390 002 002 002014009 014 009
34390 002 002 002014011 014 011
34390 002 002 002014014 014 014

________________________________________________

the formula is looking up values in colA and colB to match colA and colD on
the 2nd range respectively. Some of these answers supposed to be "N".







--
when u change the way u look @ things, the things u look at change.


"Shane Devenshire" wrote:

Hi,

It would help if you show us a small amount of data and what you are getting
as the result, and what you expect.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"EZ" wrote:

This formula is working fine, except that my data is imported from MS Access
and Excel seems not to recognize the correct data type.
I have two data sets imported from two different Access queries on the same
worksheet. I'm using my first data set/range as the base, then I have added 4
new columns that I need to populate with "Y"/"N" by looking up 2 to 3 vlaues
on my base to match them with values on the 2nd data range. So far my formula
give the correct results for some rows while giving incorrect results for
others. I have foreced the data type to match on both ranges. I also cleared
both ranges, applied my desired data type before importing the data, but
still that didn't help. I also tried to use the "--" (I use it sometimes in
SUMPRODUCT), it will change the incorrect results to the correct ones, but it
will do the same to the correct ones (change them to show incorrect results).
If I type the values over in one row I will get the correct results, but
obviously that not an option for thousands of records, and having to do that
every time we refresh the data. Is there a better way to force the formula to
ignore any data mismatch? All of my data in both ranges are either 'general'
or 'text'.

Thanks.

=IF(AND(ISNUMBER(MATCH(Z13,AC2:AC4500,0)),ISNUMBER (MATCH(AA13,AF2:AF4500,0)),ISNUMBER(MATCH(AB13,AG2 :AG4500,0))),"Y","N")

--