Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup matching conditions
Bonjour,
I have two large spreadsheets. I want to match the values in 4 cells (within the same row) in one spreadsheet and search a second spreadsheet to match the values in 4 cells (within the same row). If the 4 cells between the two spreadsheets match, I want to return a text from the second spreadsheet (from the row in which all 4 values match) to a cell in the first spreadsheet. First spreadsheet G I J O 1 Price Start Date End Date Product 2 2.62 06/08/08 06/14/08 6410036254 3 2.62 06/12/08 06/14/08 6410036260 4 25.00 06/08/08 06/13/08 6410000046 Here is my formula. It works but only with specified cell (for example 'rpt_Custom 1 '!O60, this is only 1 cell but i want to apply it to the whole spreadsheet). =VLOOKUP($O4,'rpt_Custom 1 '!C60:P60,IF(AND(I4='rpt_Custom 1 '!O60,J4<='rpt_Custom 1 '!P60,G4='rpt_Custom 1 '!N60),2,FALSE),FALSE) -- Nancy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup matching conditions
Believe you could try a multi-criteria, array-entered index/match ..
Assuming you want to return the result from col K in 'rpt_Custom 1 ' where the following criteria are simultaneously satisfied: a. O4 matches col M in 'rpt_Custom 1 ' b. Start/end dates in I4/J4 are within the start/end dates in cols O/P in 'rpt_Custom 1 ' c. G4 matches col N in 'rpt_Custom 1 ' You could paste this into say, P4's formula bar, then press CTRL+SHIFT+ENTER to confirm the formula (this is called "array-enter"): =INDEX('rpt_Custom 1 '!$K$60:$K$200,MATCH(1,($O4='rpt_Custom 1 '!$M$60:$M$200)*(I4='rpt_Custom 1 '!$O$60:$O$200)*(J4<='rpt_Custom 1 '!$P$60:$P$200)*(G4='rpt_Custom 1 '!$N$60:$N$200),0)) Then copy P4 down as far as required. Adapt the ranges to suit the actual extents, adapt the cols to point correctly in your actuals. -- Max Singapore http://savefile.com/projects/236895 Downloads:17,000 Files:358 Subscribers:55 xdemechanik --- "Nancy Guillemette" wrote: Bonjour, I have two large spreadsheets. I want to match the values in 4 cells (within the same row) in one spreadsheet and search a second spreadsheet to match the values in 4 cells (within the same row). If the 4 cells between the two spreadsheets match, I want to return a text from the second spreadsheet (from the row in which all 4 values match) to a cell in the first spreadsheet. First spreadsheet G I J O 1 Price Start Date End Date Product 2 2.62 06/08/08 06/14/08 6410036254 3 2.62 06/12/08 06/14/08 6410036260 4 25.00 06/08/08 06/13/08 6410000046 Here is my formula. It works but only with specified cell (for example 'rpt_Custom 1 '!O60, this is only 1 cell but i want to apply it to the whole spreadsheet). =VLOOKUP($O4,'rpt_Custom 1 '!C60:P60,IF(AND(I4='rpt_Custom 1 '!O60,J4<='rpt_Custom 1 '!P60,G4='rpt_Custom 1 '!N60),2,FALSE),FALSE) -- Nancy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup matching conditions
You are great!!
Merci beaucoup -- Nancy "Max" wrote: Believe you could try a multi-criteria, array-entered index/match .. Assuming you want to return the result from col K in 'rpt_Custom 1 ' where the following criteria are simultaneously satisfied: a. O4 matches col M in 'rpt_Custom 1 ' b. Start/end dates in I4/J4 are within the start/end dates in cols O/P in 'rpt_Custom 1 ' c. G4 matches col N in 'rpt_Custom 1 ' You could paste this into say, P4's formula bar, then press CTRL+SHIFT+ENTER to confirm the formula (this is called "array-enter"): =INDEX('rpt_Custom 1 '!$K$60:$K$200,MATCH(1,($O4='rpt_Custom 1 '!$M$60:$M$200)*(I4='rpt_Custom 1 '!$O$60:$O$200)*(J4<='rpt_Custom 1 '!$P$60:$P$200)*(G4='rpt_Custom 1 '!$N$60:$N$200),0)) Then copy P4 down as far as required. Adapt the ranges to suit the actual extents, adapt the cols to point correctly in your actuals. -- Max Singapore http://savefile.com/projects/236895 Downloads:17,000 Files:358 Subscribers:55 xdemechanik --- "Nancy Guillemette" wrote: Bonjour, I have two large spreadsheets. I want to match the values in 4 cells (within the same row) in one spreadsheet and search a second spreadsheet to match the values in 4 cells (within the same row). If the 4 cells between the two spreadsheets match, I want to return a text from the second spreadsheet (from the row in which all 4 values match) to a cell in the first spreadsheet. First spreadsheet G I J O 1 Price Start Date End Date Product 2 2.62 06/08/08 06/14/08 6410036254 3 2.62 06/12/08 06/14/08 6410036260 4 25.00 06/08/08 06/13/08 6410000046 Here is my formula. It works but only with specified cell (for example 'rpt_Custom 1 '!O60, this is only 1 cell but i want to apply it to the whole spreadsheet). =VLOOKUP($O4,'rpt_Custom 1 '!C60:P60,IF(AND(I4='rpt_Custom 1 '!O60,J4<='rpt_Custom 1 '!P60,G4='rpt_Custom 1 '!N60),2,FALSE),FALSE) -- Nancy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup matching conditions
Welcome, Nancy
-- Max Singapore http://savefile.com/projects/236895 Downloads:17,000 Files:358 Subscribers:55 xdemechanik --- "Nancy Guillemette" wrote in message ... You are great!! Merci beaucoup -- Nancy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Conditions + Sum of a colum matching those conditions | Excel Worksheet Functions | |||
vlookup/matching? much help needed! | Excel Discussion (Misc queries) | |||
vlookup 2nd matching | Excel Discussion (Misc queries) | |||
Help with VLOOKUP or other matching function | Excel Worksheet Functions | |||
If's conditions about matching | Excel Worksheet Functions |