First, my apologies. Think I missed including col E within the MATCH lookup
array earlier. I had a double instead for col D.

This part:
... *(Sheet1!D$3:D$7000=D3)*(Sheet1!D$3:D$7000=D3)* ...

should have read as:
... *(Sheet1!D$3:D$7000=D3)*(Sheet1!E$3:E$7000=E3)* ...

Trust the above typo was caught / corrected
in the earlier expressions when you applied them


As for why a "1" is used as the lookup value within:
MATCH(1, lookup array, 0)

It's because the lookup array in the MATCH, this part:
(Sheet1!A$3:A$7000=A3)*(Sheet1!B$3:B$7000=B3)*(... )*(...)*..
will ultimately resolve to an array of ones/zeros, eg: {0;0;1;0;0;0;0;...}
depending on where the multiple criteria is satisfied (1's), or not (0's)

Using the lookup value: 1 in MATCH would hence give us the (1st) matching
position within the array where the multiple criteria is satisfied. It's
presumed of course, that there should be only a single matching position (ie
a single/unique instance of 1) to be returned within the array. That position
returned (a number) is then used by the INDEX part of the expression, eg:
INDEX(Sheet1!G$3:G$7000, ...) to yield the required result. Hope the above
clarifies it.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eddy Stan" wrote:
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.