vlookup or dget with multiple condition - help please

[Eddy Stan]

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.

 

[Max]

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$7000=A3)*(Sheet1!B$3:B$7000=B3)*(Sheet1!C$3:C$7000=C3)*(Sheet1!D$3$7000=D3)*(Sheet1!D$3$7000=D3)*(Sheet1!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$7000=D3)*(Sheet1!D$3$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$7000=D3)*(Sheet1!D$3$7000=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]

Oops, small typo here:

.. array-enter the formula by pressing CRL+SHIFT+ENTER:

it should read as:

.. array-enter the formula by pressing CTRL+SHIFT+ENTER:

If you do the array-entering correctly, Excel will wrap curly braces { }
around the formula (look within the formula bar). Use this as a visual check
that the array-entering is correctly done, before you copy down. If you don't
see the curlies, you have to try again. Re-click inside the formula bar,
press CTRL+SHIFT+ENTER.

 

[Eddy Stan]

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]

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$7000=D3)*(Sheet1!D$3$7000=D3)* ...

should have read as:
... *(Sheet1!D$3$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.