Using VLOOKUP formula

[Mahadevan Swamy]

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

 

[Peo Sjoblom]

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?

 

[Mahadevan Swamy]

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

 

[Peo Sjoblom]

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)*(D313=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

 

[Mahadevan Swamy]

why is look_up value parameter of MATCH function = 1?

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)*(D313=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

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

 

[Peo Sjoblom]

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

why is look_up value parameter of MATCH function = 1?

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)*(D313=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

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