Depending on how worksheet 1 is currently laid out...
If it looks like:
Rel Small MED Large
May 10 30-Nov-09 01-Oct-09 01-Sep-09
Aug 10 01-Feb-10 01-Jan-10 01-Dec-09
Nov 10 01-Apr-10 01-Mar-10 01-Feb-10
Then you should be able to use =index(match(),match())
Debra Dalgleish shares info:
http://www.contextures.com/xlFunctions03.html
especially example 3:
http://www.contextures.com/xlFunctio...ml#IndexMatch2
========
If your data still looks like:
Size Rel Month Deadline
Small May 10 30-Nov-09
MED May 10 01-Oct-09
Large May 10 01-Sep-09
Then...
You have a few choices...
One is to insert a new column A in the table worksheet.
Then you could concatenate the values in the new column B and C into column A.
=b2&"|"&c2
(and drag down)
Then you could modify the =vlookup() to look at this new column--but concatenate
the year and name into the value to match:
=vlookup(a2&"|"&b2,othersheet!a:e,3,false)
where a2 holds the year and b2 holds the name.
There are other ways, too.
If you want exact matches for just two columns (and return a value from a
third), you could use:
=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))
(all in one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
Adjust the range to match--but you can only use the whole column in xl2007.
This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.
And you can add more conditions by just adding more stuff to that product
portion of the formula:
=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))
============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:
=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))
lcc wrote:
Hi
Wonder if you can help as i cant get my head around this. I have tried
to use a combination of match, index, vlookup but just cant get the
combination right, and now I am totally lost and even a basic vlookup
isnt working
I have a spreadsheet which contains a list of projects. Within this i
have cells where you select a project size (s,m,l), enter a release
month, then dependant on the combination of the 2 cells (project size
and Rel month) the 3rd cell should display the relevant deadline date.
Worksheet 1
contains a list of defined project sizes, release dates and deadline
date (The rel month and deadline data could change)
Size Rel Month Deadline
Small May 10 30-Nov-09
MED May 10 01-Oct-09
Large May 10 01-Sep-09
Small Aug 10 01-Feb-10
MED Aug 10 01-Jan-10
Large Aug 10 01-Dec-09
Small Nov 10 01-Apr-10
MED Nov 10 01-Mar-10
Large Nov 10 01-Feb-10
(i have tried the array list in a couple ways but no joy)
Rel Small MED Large
May 10 30-Nov-09 01-Oct-09 01-Sep-09
Aug 10 01-Feb-10 01-Jan-10 01-Dec-09
Nov 10 01-Apr-10 01-Mar-10 01-Feb-10
Worksheet 2
contains the larger listing of projects and within this i have cells
where you select a project size, enter a release month, then dependant
on the combination of the 2, the 3rd cell should automatically display
the relevant due date
I tried various combintions of h/vlookup in the 3rd cell refering to
the data on worksheet 1, but I just seem to have got lost
eg results should be
Size Rel Date DEADLINE
Small May 10 30-Nov-09
Med Nov 10 01-Mar-10
Large Aug 10 01-Dec-09
Hoping this explains
Hoping you can assist
L
--
Dave Peterson