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