vlookup or dget with multiple condition - help please
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. |
vlookup or dget with multiple condition - help please
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. |
vlookup or dget with multiple condition - help please
Oops, small typo he
.. 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
vlookup or dget with multiple condition - help please
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. |
vlookup or dget with multiple condition - help please
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. |
All times are GMT +1. The time now is 02:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com