EXCEL LOOKUP

[Brendy]

Hi I have an excel workbook with 2 sheets, the first sheet has all the
information in it and the second is where I want the results displayed.
In sheet 1 have a row of dates (52 weeks of the year) in cells B1:BA1,
In column A starting at A2:A173 I have a list of names and in cells
B2:BA173 I have a list of duty numbers. In sheet 2 I have a formatted
form and on that form I want to create a formula that let's me enter a
date and a duty and return the name of the person doing that duty.

EG A B C D
1 2/1/06 9/1/06 16/1/06
2 JOHN 1 2 3
3 BILLY 2 3 1
4 TOM 3 1 2


If in sheet 2 I ENTERED 2/1/06 and duty 2, the result should be
BILLY

I have been wrecking my brains for weeks trying to solve this but
Can't, it would be much appreciated if someone could help me THANK YOU.

 

[vezerid]

Date in K1, duty in L1.

=INDEX('Sheet1'!$A$2:$A$173, MATCH($L$1, OFFSET('Sheet1'!$A$2:$A$173,
0, MATCH($K$1, 'Sheet1'!$B$2:$BA$2,0)),0)

HTH
Kostis Vezerides

 

[Brendy]

Thanks for your reply vezerid but the result I get is #N/A

 

[vezerid]

Apologies... I should have tested the formula first. This one should
work:
=INDEX(Sheet1!$A$2:$A$171,MATCH($L$1,OFFSET(Sheet1!$A$2:$A$171,0,MATCH($K$1,Sheet1!$B$1:$BA$1,0)),0))

HTH
Kostis Vezerides

 

[Brendy]

Thanks vezerid your a star, the formula works, now I have the task of
understanding how you did it , thanks again

 

[Guest]

Just one minor change...

=INDEX('Sheet1'!$A$2:$A$173, MATCH($L$1, OFFSET('Sheet1'!$A$2:$A$173,0,
MATCH($K$1, 'Sheet1'!$B$1:$BA$1,0)),0)

 

[vezerid]

INDEX(array, pos) gives you the element in the array found in position
pos.
MATCH(value, array) is the reverse, it tells you in which position
value is.
OFFSET(range, row, col,..., ...) returns a reference to a range which
is offset.
Basically the formula does the following:
The inner MATCH() finds the position of the date in your date headers.
It then computes the offset of the original column by as many columns
as the position of the date.
We then try to find the position of the duty in the offset column.
This position is used in INDEX of the original array with the names to
return the corresponding name.

Glad to be of help.

Kostis Vezerides

 

[Brendy]

vezerid could you modify this so I can copy the formula down the column
without getting #N/A if the cell were the date and duty go is blank. I
have tried to add the function IF and ISNA to your formula but with no
success.

Thanks again

 

[vezerid]

If I understand you correctly, this is an altogether different problem.

Now, for a single date and a blank duty, what exactly do you want to
retrieve?
All the persons scheduled for that day? This is very different and
requires a completely different logic.
Please be more specific. Also, soon I will have to go, so maybe someone
else can answer if I am gone after you answer.

Regards,
Kostis