# Maybe a Lookup Formula

#### paulrm906

##### Active Member
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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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))))

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

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))))

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

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?

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

Replies
7
Views
147
Replies
13
Views
343
Replies
3
Views
330
Replies
2
Views
423
Replies
12
Views
246

1,219,519
Messages
6,148,755
Members
450,833
Latest member
Andyboi

### 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.

### Which adblocker are you using?

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

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