lookup help. lookup result based on data in 2 columns

[lcc]

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]

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/xlFunctions03.html#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))

 

[Eduardo]

Hi,
try

=INDEX($C$1:$C$9,MATCH(1,($A12=$B$1:$B$9)*(B$11=$A$1:$A$9),0))

Enter the formula as an array, CTRL + SHIFT + ENTER

copy formula

C1:C9 is where the data to be retrieved is
A12 is the real date
B1:B9 is where the real dates of your data are
B11 is the size
A1:A9 is where the size are in your data

change it to fit your needs

 

[lcc]

Hi,
try

=INDEX($C$1:$C$9,MATCH(1,($A12=$B$1:$B$9)*(B$11=$A$1:$A$9),0))

Enter the formula as an array, CTRL + SHIFT + ENTER

copy formula

C1:C9 is where the data to be retrieved is
A12 is the real date
B1:B9 is where the real dates of your data are
B11 is the size
A1:A9 is where the size are in your data

change it to fit your needs














- Show quoted text -

Thank you so much guys it seemed to have worked, Had to get use to
doing the Ctrl+shift+Enter to make it to work but got there in the
end, it was so simple once i had it explained, thanks heaps. Tme for
me to brush up my skills on a course i think
Thanks again