Hi Max,
Superb. I am really happy.
Generally when we use match() function we give match(lukupvalue, array, 0)
but you have 1 in the lookup value. why how does the match know what i am
looking for, actually in the array you have given my lookup value !.
Secondly the index() - you gave the result array, match function to find
row, but didnot give anything to find rowno. is it because the gave the
result array in start.
Can you explain me how it worked, if possible, in a logical way, so that i
can think in the same way next time.
Thank you very much.


"Max" wrote:

Assume identically structured data in Sheets 1 and 2, cols A to F, data from
row3 down to row7000?

In Sheet2 (for August),

Put this in G3 and array-enter the formula by pressing CRL+SHIFT+ENTER:
=INDEX(Sheet1!G$3:G$7000,MATCH(1,(Sheet1!A$3:A$700 0=A3)*(Sheet1!B$3:B$7000=B3)*(Sheet1!C$3:C$7000=C3 )*(Sheet1!D$3:D$7000=D3)*(Sheet1!D$3:D$7000=D3)*(S heet1!F$3:F$7000=F3),0))
Copy G3 down as far as required.

And if you want an error trap to return zeros for any non-matches found, put
this instead in G3, array-enter, copy down:
=IF(ISNA(MATCH(1,(Sheet1!A$3:A$7000=A3)*(Sheet1!B$ 3:B$7000=B3)*(Sheet1!C$3:C$7000=C3)*(Sheet1!D$3:D$ 7000=D3)*(Sheet1!D$3:D$7000=D3)*(Sheet1!F$3:F$7000 =F3),0)),0,INDEX(Sheet1!G$3:G$7000,MATCH(1,(Sheet1 !A$3:A$7000=A3)*(Sheet1!B$3:B$7000=B3)*(Sheet1!C$3 :C$7000=C3)*(Sheet1!D$3:D$7000=D3)*(Sheet1!D$3:D$7 000=D3)*(Sheet1!F$3:F$7000=F3),0)))

Adapt the ranges to suit your actuals ..

Since the above is quite calc intensive, you might want to set the calc mode
to manual before proceeding. Click Tools Options Calculation tab Check
"Manual" OK. Then do the formula fills, and press F9 to recalc when ready
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eddy Stan" wrote:
Hi
I have 2 sheets
sheet1 has july data and sheet2 has august data (7000 rows data)
each with 8 column data
8th column has the status value like good, spoiled, damaged, repaired.. so
on (8values)
the data in july & august are not in the regular row order.
So in the august sheet col A to F has to be searched with July A to F colum
and if everything matches i need value in July G column to be placed in Aug
G col.
i want the function to be placed in Aug sheet G colum. So that i can copy
the forumula down 7000 rows, if doesnt match then zero or error value need to
be displayed.
So far i tackle this issue like a3&b3&c3&d3&e3&f3 in column A (inserted
colum) in july and similarly in August sheet, then use vlookup()
Advance thanks for help.