Maybe a Lookup Formula

paulrm906

Active Member
Joined
Jan 26, 2006
Messages
329
Hello everyone
I have another formula that I really do not know where to start with, what I am trying to do is; In column "D" is the staff ID number and then in row "1" from column "O" to "S" is the date.
Sheet one
Column
D_________O______P_____Q_____R______S
ID No........1-10.......2-10.......3-10......4-10......5-10
21
22
23
24
25

Sheet two
is the staff graphic. with an example below.

Column
A...............B...........C..........D..........E..........F
ID ........1-10.....2-10.....3-10.....4-10.....5-10
21...........X..........X..........5..........4.........UP
22...........H..........H..........H..........H.........5
23...........5..........5..........4..........4.........X
24...........X..........X..........2..........2..........2

Now what I need to do is enter a formula in cell "O2" then match the ID number in D2 and the date in cell "O1". then look in the staff graphic in sheet two find the staff members ID number in column "A" and match the date in the first row. But in sheet one I only want to know if some one is rostered to "X", "UP" or "H" if it is not one of those then do nothing.
I would like to thank any one for any suggestions or help in advance and of cause I will be also sending a big thankyou to those that can help me solve this.

Paul
Moscow
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Book8
DEFGHIJKLMNOPQRS
1ID No1-Oct-062-Oct-063-Oct-064-Oct-065-Oct-06
221XX  UP
322HHHH 
423    X
524XX   
625XX   
Sheet1


O2, copied across and down:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX({"X","H","UP"},MATCH(INDEX(Sheet2!$B$2:$F$5,MATCH($D2,Sheet2!$A$2:$A$5,1),MATCH(O$1,Sheet2!$B$1:$F$1,1)),{"X","H","UP"},0))))
 

paulrm906

Active Member
Joined
Jan 26, 2006
Messages
329
Hello Aladin

Firstly thanks for your reply. I have been trying all evening to get your suggestion to work. I have made the changes that had to be made and have pasted the formula below as it now appears in my program. First off I should of mentioned that in sheet two (Graphic) every cell contains a formula. It is just a simple formula which is linked to the origanal graphic and so forth. and it is just the = to formula, example A1 in this program of mine = A1 in the origanal graphic. Secondly I did get the formula to work slightly by changing the range number from 300 to just 9 and it worked but it was giving me the result for the row 2 down. But when I again extended the range it would not work. Now I should also mention that some rows are empty. I don't know if thats makes any differance.
I hope this makes sense to you and again thanks for your help.

=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX({"X";"H";"UP"},MATCH(INDEX(Graphic!$CR$2:$GE$300,MATCH($D16,Graphic!$C$4:$C$300,1),MATCH(O$2,Graphic!$CR$2:$GE$2,1)),{"X";"H";"UP"},0))))

Paul
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Hello Aladin

Firstly thanks for your reply. I have been trying all evening to get your suggestion to work. I have made the changes that had to be made and have pasted the formula below as it now appears in my program. First off I should of mentioned that in sheet two (Graphic) every cell contains a formula. It is just a simple formula which is linked to the origanal graphic and so forth. and it is just the = to formula, example A1 in this program of mine = A1 in the origanal graphic. Secondly I did get the formula to work slightly by changing the range number from 300 to just 9 and it worked but it was giving me the result for the row 2 down. But when I again extended the range it would not work. Now I should also mention that some rows are empty. I don't know if thats makes any differance.
I hope this makes sense to you and again thanks for your help.

=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX({"X";"H";"UP"},MATCH(INDEX(Graphic!$CR$2:$GE$300,MATCH($D16,Graphic!$C$4:$C$300,1),MATCH(O$2,Graphic!$CR$2:$GE$2,1)),{"X";"H";"UP"},0))))

Paul

=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX({"X";"H";"UP"},MATCH(INDEX(Graphic!$CR$2:$GE$300,MATCH($D16,Graphic!$C$4:$C$300,0),MATCH(O$2,Graphic!$CR$2:$GE$2,0)),{"X";"H";"UP"},0))))
 

paulrm906

Active Member
Joined
Jan 26, 2006
Messages
329

ADVERTISEMENT

Hello again Aladin
I have again tried your new suggestion and I still keep getting the results for the 2 rows above. I have looked has close as I can but can not see what is wrong.
And again thanks for your help and support.


Paul
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Hello again Aladin
I have again tried your new suggestion and I still keep getting the results for the 2 rows above. I have looked has close as I can but can not see what is wrong.
And again thanks for your help and support.


Paul

Try to rebuild the exhibit I posted using the data you provided before applying the formula to your original data:
Book8
ABCDEF
1ID1-Oct-062-Oct-063-Oct-064-Oct-065-Oct-06
221XX54UP
322HHHH5
4235544X
524XX222
6
7
Sheet2
Book8
DEFGHIJKLMNOPQRS
1ID No1-Oct-062-Oct-063-Oct-064-Oct-065-Oct-06
221XX  UP
322HHHH 
423    X
524XX   
625XX   
7
Sheet1


O2, copied across and down:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX({"X","H","UP"},MATCH(INDEX(Sheet2!$B$2:$F$5,MATCH($D2,Sheet2!$A$2:$A$5,1),MATCH(O$1,Sheet2!$B$1:$F$1,1)),{"X","H","UP"},0))))

This formula uses 1 in MATCH for your sample (Sheet2) is in ascending order.

Why don't you rebuild the foregoing in a separate workbook in order to get an idea how it's supposed to work before you implement it in your actual workbook?
 

paulrm906

Active Member
Joined
Jan 26, 2006
Messages
329
Hello Aladin

Just want to let you know I finally got the above formula working and it was not working because I failed to notice one sentence of yours which was:
This formula uses 1 in MATCH for your sample (Sheet2) is in ascending order.
And then it was solved due to you helping icey_69 yesterday so again thanks very much.

Paul
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top