| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using VLOOKUP formula
How can I use VLOOKUP and MATCH functions to substitute this formula?
=SUM(IF('[Input.xls]Customer Returns (External)'!$A$3:$A$1200 = $C $53, IF('[Input.xls]Customer Returns (External)'!$C$3:$C$1200=C$54, IF('[Input.xls]Customer Returns (External)'!$D$3:$D$1200 = $A57, IF(ISBLANK('[Input.xls]Customer Returns (External)'!$J$3:$J$1200), IF(ISBLANK('[Input.xls]Customer Returns (External)'!$I$3:$I$1200), '[Input.xls]Customer Returns (External)'!$H$3:$H$1200, '[Input.xls]Customer Returns (External)'!$I$3:$I$1200), '[Input.xls]Customer Returns (External)'!$J$3:$J$1200))))) Any ideas? Thanks in advance Swamy |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using VLOOKUP formula
Maybe it would be easier if you explained what you are trying to do instead
of asking people to audit a large array formula trying to figure out what you want? -- Regards, Peo Sjoblom "Mahadevan Swamy" wrote in message ups.com... How can I use VLOOKUP and MATCH functions to substitute this formula? =SUM(IF('[Input.xls]Customer Returns (External)'!$A$3:$A$1200 = $C $53, IF('[Input.xls]Customer Returns (External)'!$C$3:$C$1200=C$54, IF('[Input.xls]Customer Returns (External)'!$D$3:$D$1200 = $A57, IF(ISBLANK('[Input.xls]Customer Returns (External)'!$J$3:$J$1200), IF(ISBLANK('[Input.xls]Customer Returns (External)'!$I$3:$I$1200), '[Input.xls]Customer Returns (External)'!$H$3:$H$1200, '[Input.xls]Customer Returns (External)'!$I$3:$I$1200), '[Input.xls]Customer Returns (External)'!$J$3:$J$1200))))) Any ideas? Thanks in advance Swamy |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using VLOOKUP formula
Ok. I am having two workbooks: One workbook is Input.xls and the other
is Report1.xls. Report1.xls takes all the info from input.xls and return a particular value from the corresponding month, customer and defect code. The column headings for input.xls are A - Month C - Customer D - Defect code G - Incidents H - Reported I - Returned J - Confirmed Defects The first condition evaluates the array (from input.xls) for month and checks if it matches the one in report1.xls. '[Input.xls]Customer Returns (External)'!$A$3:$A$1200 = $C$53 The second condition evaluate the array for customer and checks if it is true. '[Input.xls]Customer Returns (External)'!$C$3:$C$1200=C$54 The third condition evaluates the array for Defect Code and checks if it is true. '[Input.xls]Customer Returns (External)'!$D$3:$D$1200 = $A57 The fourth condition checks is column J is blank or not If it is blank, then check if column I is blank or not, If true then return column H or else return column I Or else return value from column J. Is it possible to do all these using VLOOKUP and MATCH functions? I want to reduce my formula to the shortest form possible as the file takes a huge time when opened. Any ideass? Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using VLOOKUP formula
Not possible using VLOOKUP, you can create a shorter formula using INDEX and
MATCH but will it be faster for the lookup in H something like =INDEX(H3:H13,MATCH(1,(A3:A13=E1)*(C3:C13=F1)*(D3: D13=G1)*(J3:J13<"")*(I3:I13<""),0)) however to return values from 3 different columns depending on empty/blank cells in J and in I would require if functions in one way or the other. Maybe you could use a filter instead or multiple formulas -- Regards, Peo Sjoblom "Mahadevan Swamy" wrote in message oups.com... Ok. I am having two workbooks: One workbook is Input.xls and the other is Report1.xls. Report1.xls takes all the info from input.xls and return a particular value from the corresponding month, customer and defect code. The column headings for input.xls are A - Month C - Customer D - Defect code G - Incidents H - Reported I - Returned J - Confirmed Defects The first condition evaluates the array (from input.xls) for month and checks if it matches the one in report1.xls. '[Input.xls]Customer Returns (External)'!$A$3:$A$1200 = $C$53 The second condition evaluate the array for customer and checks if it is true. '[Input.xls]Customer Returns (External)'!$C$3:$C$1200=C$54 The third condition evaluates the array for Defect Code and checks if it is true. '[Input.xls]Customer Returns (External)'!$D$3:$D$1200 = $A57 The fourth condition checks is column J is blank or not If it is blank, then check if column I is blank or not, If true then return column H or else return column I Or else return value from column J. Is it possible to do all these using VLOOKUP and MATCH functions? I want to reduce my formula to the shortest form possible as the file takes a huge time when opened. Any ideass? Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using VLOOKUP formula
why is look_up value parameter of MATCH function = 1?
On Jun 28, 3:22 pm, "Peo Sjoblom" wrote: Not possible using VLOOKUP, you can create a shorter formula using INDEX and MATCH but will it be faster for the lookup in H something like =INDEX(H3:H13,MATCH(1,(A3:A13=E1)*(C3:C13=F1)*(D3: D13=G1)*(J3:J13<"")*(I3:I13<""),0)) however to return values from 3 different columns depending on empty/blank cells in J and in I would require if functions in one way or the other. Maybe you could use a filter instead or multiple formulas -- Regards, Peo Sjoblom "Mahadevan Swamy" wrote in message oups.com... Ok. I am having two workbooks: One workbook is Input.xls and the other is Report1.xls. Report1.xls takes all the info from input.xls and return a particular value from the corresponding month, customer and defect code. The column headings for input.xls are A - Month C - Customer D - Defect code G - Incidents H - Reported I - Returned J - Confirmed Defects The first condition evaluates the array (from input.xls) for month and checks if it matches the one in report1.xls. '[Input.xls]Customer Returns (External)'!$A$3:$A$1200 = $C$53 The second condition evaluate the array for customer and checks if it is true. '[Input.xls]Customer Returns (External)'!$C$3:$C$1200=C$54 The third condition evaluates the array for Defect Code and checks if it is true. '[Input.xls]Customer Returns (External)'!$D$3:$D$1200 = $A57 The fourth condition checks is column J is blank or not If it is blank, then check if column I is blank or not, If true then return column H or else return column I Or else return value from column J. Is it possible to do all these using VLOOKUP and MATCH functions? I want to reduce my formula to the shortest form possible as the file takes a huge time when opened. Any ideass? Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using VLOOKUP formula
Because if all those criteria are TRUE, when multiplied they will return 1,
each array will return either TRUE or FALSE but when multiplied they will return 1 or 0 -- Regards, Peo Sjoblom "Mahadevan Swamy" wrote in message ups.com... why is look_up value parameter of MATCH function = 1? On Jun 28, 3:22 pm, "Peo Sjoblom" wrote: Not possible using VLOOKUP, you can create a shorter formula using INDEX and MATCH but will it be faster for the lookup in H something like =INDEX(H3:H13,MATCH(1,(A3:A13=E1)*(C3:C13=F1)*(D3: D13=G1)*(J3:J13<"")*(I3:I13<""),0)) however to return values from 3 different columns depending on empty/blank cells in J and in I would require if functions in one way or the other. Maybe you could use a filter instead or multiple formulas -- Regards, Peo Sjoblom "Mahadevan Swamy" wrote in message oups.com... Ok. I am having two workbooks: One workbook is Input.xls and the other is Report1.xls. Report1.xls takes all the info from input.xls and return a particular value from the corresponding month, customer and defect code. The column headings for input.xls are A - Month C - Customer D - Defect code G - Incidents H - Reported I - Returned J - Confirmed Defects The first condition evaluates the array (from input.xls) for month and checks if it matches the one in report1.xls. '[Input.xls]Customer Returns (External)'!$A$3:$A$1200 = $C$53 The second condition evaluate the array for customer and checks if it is true. '[Input.xls]Customer Returns (External)'!$C$3:$C$1200=C$54 The third condition evaluates the array for Defect Code and checks if it is true. '[Input.xls]Customer Returns (External)'!$D$3:$D$1200 = $A57 The fourth condition checks is column J is blank or not If it is blank, then check if column I is blank or not, If true then return column H or else return column I Or else return value from column J. Is it possible to do all these using VLOOKUP and MATCH functions? I want to reduce my formula to the shortest form possible as the file takes a huge time when opened. Any ideass? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002 VLOOKUP formula or other formula | Excel Discussion (Misc queries) | |||
| a possible vlookup formula....help | Excel Discussion (Misc queries) | |||
| Vlookup formula | Excel Worksheet Functions | |||
| How do I set up vlookup formula. | Excel Worksheet Functions | |||
| vlookup formula | Excel Worksheet Functions | |||
Linear Mode
