ISERROR AND MATCH
Posted by YANECKC on August 03, 2001 11:16 AM
I HAVE A SPREADSHEET WITH COLUMNS A THRU G.
I HAVE A FORMULA IN E1 =IF(ISERROR(MATCH(A1, $F$1:$F$405, 0)), "", F1)
I WOULD LIKE TO COMBINE WITH =IF(ISERROR(MATCH(A1, $G$1:$G$405, 0)), "", G1)
I AM COMPARING COLUMN A1 WITH F IF MATCH SHOW F1 WHICH = RECMP.
IF COLUMN A1 = G THEN SHOW G1 WHICH = RECEP
IF COLUMN A1 = F & G THEN SHOW THE WORD BOTH
BELOW IS ALITTLE OF SPREADSHEET
===========================================
ID # SYMBOL HOUSE ID # DESCRIPTION REPORT RECMP RECEP
015565831 ALBAX A002963 **ALGER FUND RECMP L00215373 L00215647
015565849 ACAAX A002964 **ALGER FUND-CAPITAL RECMP L00215472 L00216116
015565864 AMGAX A002965 **ALGER FUND RECMP L00215522 L0022E100
015565856 ALGAX A002966 **ALGER FUND RECMP L00215662 L0022E118
015565872 ALSAX A002967 **ALGER FUND-SMALL L00215670 L0022R101
L00215159 ASMAZ A003031 ***ACM SHORT MATURITY L00215860 L0022R200
004322780 ACSIX A006317 **ACCESSOR FUND INC 015565500 921937108
004322814 AGRIX A006320 **ACCESSOR FUND INC 015565609 921937108
046905402 ASFVZ A007106 **ATALANTA / SOSNOFF TR 015565708 921937108
046905204 ASFGZ A007158 **ATALANTA / SOSNOFF TR 015565773 921937108
03071Q597 JMPIX A007425 **AMERIPRIME FDS 015565781 921937207
03152E208 AMDAX A007997 **AMIDEX FDS INC 015565799 921937207
032168320 ICGAX A008196 **AMSOUTH FDS 015565807 921937306
L00215811 ACGAZ A008341 ***ACM GLOBAL INVESTMENTS 015565815 921937405
004322798 AVUIX A008950 **ACCESSOR FUND INC 015565823 921938106
017216300 VERDX A009343 **ALLEGHANY FDS 015565831 921939203
004322715 AHBIX A009586 **ACCESSOR FDS INC 015565849 921948105
004322723 AHBAX A009592 **ACCESSOR FDS INC 015565856 922018106
03071Q613 CNCVX A009728 **AMERIPRIME FUNDS 015565864 922018205
SO PROBLEM IS COMBINING THE TWO FORMULAS AND ADD TO IT
IF A1 = F & G SHOW THE WORD BOTH.
Posted by Aladin Akyurek on August 03, 2001 12:31 PM
Yaneck, Why do you want to return always the value of F1, no matter which lookup value is in A1?
The same question as above, now it concerns G1.
I believe I don't understand your comparison. Care to explain?
Posted by YANECKC on August 03, 2001 12:59 PM
ALADIN
THE VALUE OF F1 = RECMP
I MEAN IF THE MATCH FINDS A NUMBER FROM COLUMN A AND ANY WHERE IN COLUMN F IT INSERTS THE WORD RECMP IN COLUMN E
THE VALUE OF G1 - RECEP
I MEAN IF THE MATCH FINDS A NUMBER FROM COLUMN A AND ANY WHERE IN COLUMN G IT INSERTS THE WORD RECEP IN COLUMN E
IF THERE IS A MATCH OF COLUMN A AND F AND G IN NEED THE WORD BOTH IN COLUMN E
SEE I'M DEALING WTH THREE REPORTS
COLUMN A THRU D IS ONE REPORT CALLED ID #
COLUMN F IS ANOTHER REPORT CALLED RECMP
COLUMN G IS ANOTHER REPORT CALLED RECEP
I'M COMPARING REPORT ID #(COLUMN A) TO RECMP(COLUMN F) THEN TO RECEP (COLUMN G)
I USE COLUMN E TO DESCRIB WHICH REPORT THE MATCH WAS FOUND ON.
THAT WHY I HAVE TO COMBINE THE TWO FORMULAS AND ALSO PRINT THE WORD BOTH IF THE
MATCH IS FOUND ON RECMP AND RECEP.
THANK YOU FOR YOUR QUICK RESPONSE SO ALL I NEED IS A FORMULA TO GO INTO COLUMN E
DESCRIBING WHICH COLUMNS WERE MATCHED.
Posted by Aladin Akyurek on August 03, 2001 1:31 PM
Ok, Yaneck.
=IF(AND(ISNUMBER(MATCH(A1,$F$1:$F$405,0)), ISNUMBER(MATCH(A1,$G$1:$G$405,0))), "BOTH", IF(ISNUMBER(MATCH(A1,$F$1:$F$405,0)), F1,IF(ISNUMBER(MATCH(A1,$G$1:$G$405,0)), G1, "")))
Aladin
Posted by YANECKC on August 03, 2001 2:00 PM
ALADIN
GREAT JOB! ONCE AGAIN THANK YOU FOR ALL YOUR HELP!
KEEP UP THE GREAT WORK! BECAUSE OF YOUR HELP YOU MAKE IT A GREAT SITE!
YANECKC
