| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
choose match formula
I am running a workbook with various versions of the below formula:
=CHOOSE(MATCH($D$5,{"Select reporting Month ","JAN07","FEB07","MAR07","APR07","MAY07","JUN07", "JUL07","AUG07","SEP07","OCT07","NOV07","DEC07"},0 ),JAN07!F14,JAN07!E$11,FEB07!E$11,MAR07!E$11,APR07 !E$11,MAY07!E$11,JUN07!E$11,JUL07!E$11,AUG07!E$11, SEP07!E$11,OCT07!E$11,NOV07!E$11,DEC07!E$11) I need to make the next formula in the same manner. I need to include all 12 months =IF(ISERROR(IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB6 00,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB6 00,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB6 00,4,FALSE),"No Data")))),"No Responses",IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB60 0,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB60 0,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB60 0,4,FALSE),"No Data")))) Thanks for any help |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
choose match formula
You could try the INDIRECT() function
=VLOOKUP(D6,INDIRECT(D5&"!D16:AB600"),4,FALSE) "Scott@CW" wrote: I am running a workbook with various versions of the below formula: =CHOOSE(MATCH($D$5,{"Select reporting Month ","JAN07","FEB07","MAR07","APR07","MAY07","JUN07", "JUL07","AUG07","SEP07","OCT07","NOV07","DEC07"},0 ),JAN07!F14,JAN07!E$11,FEB07!E$11,MAR07!E$11,APR07 !E$11,MAY07!E$11,JUN07!E$11,JUL07!E$11,AUG07!E$11, SEP07!E$11,OCT07!E$11,NOV07!E$11,DEC07!E$11) I need to make the next formula in the same manner. I need to include all 12 months =IF(ISERROR(IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB6 00,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB6 00,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB6 00,4,FALSE),"No Data")))),"No Responses",IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB60 0,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB60 0,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB60 0,4,FALSE),"No Data")))) Thanks for any help |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
choose match formula
it works!!!!
you rock!!!! "Duke Carey" wrote: You could try the INDIRECT() function =VLOOKUP(D6,INDIRECT(D5&"!D16:AB600"),4,FALSE) "Scott@CW" wrote: I am running a workbook with various versions of the below formula: =CHOOSE(MATCH($D$5,{"Select reporting Month ","JAN07","FEB07","MAR07","APR07","MAY07","JUN07", "JUL07","AUG07","SEP07","OCT07","NOV07","DEC07"},0 ),JAN07!F14,JAN07!E$11,FEB07!E$11,MAR07!E$11,APR07 !E$11,MAY07!E$11,JUN07!E$11,JUL07!E$11,AUG07!E$11, SEP07!E$11,OCT07!E$11,NOV07!E$11,DEC07!E$11) I need to make the next formula in the same manner. I need to include all 12 months =IF(ISERROR(IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB6 00,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB6 00,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB6 00,4,FALSE),"No Data")))),"No Responses",IF(D5="APR07",VLOOKUP(D6,APR07!D16:AB60 0,4,FALSE),IF(D5="MAY07",VLOOKUP(D6,MAY07!D16:AB60 0,4,FALSE),IF(D5="JUN07",VLOOKUP(D6,JUN07!D16:AB60 0,4,FALSE),"No Data")))) Thanks for any help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with lookups (Index/Offset/Match/Choose???) | Excel Discussion (Misc queries) | |||
| formula to choose a hyperlink from a cell | Excel Worksheet Functions | |||
| How to indicate in formula to choose the entire column starting from a specified cell? | Excel Discussion (Misc queries) | |||
| VLOOKUP, INDEX, MATCH... What to choose | Excel Worksheet Functions | |||
| Choose between two cells the lower result of a formula | Excel Worksheet Functions | |||
Linear Mode
