lookup help. lookup result based on data in 2 columns
On Apr 5, 10:48*pm, Eduardo wrote:
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" 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
.- Hide quoted text -
- 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
|