Vlookup matching conditions

[Nancy Guillemette]

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 '!C6060,IF(AND(I4>='rpt_Custom 1
'!O60,J4<='rpt_Custom 1 '!P60,G4='rpt_Custom 1 '!N60),2,FALSE),FALSE)

 

[Max]

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]

You are great!!

Merci beaucoup
--
Nancy

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

 

[Max]

Welcome, Nancy
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik