jcarroll01
Board Regular
- Joined
- Jun 17, 2005
- Messages
- 122
I have a formula that looks up a value in a column based on finding "lunch" in another column....I need to find a way to return another value if the "Lunch" line item is missing.
If the employe above is missing the "lunch" line item...the forula keeps looking until it finds "lunch" further down the sheet.
Here is the entire formula from column "L" - I then do a simple lookup from another sheet to get the scores per person.
=INDEX($H$1:$H$3000,MIN(IF($A$1:$A$3000="lunch",IF(ROW($H$1:$H$3000)-ROW($H$1)+1>MATCH($N18,$A$1:$A$3000,0),ROW($H$1:$H$3000)-ROW($H$1)+1,""))))
Macro-Master.xls | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
16 | 24573 | Ahsan,Sofia | Date:06/0 | 6-May | LunchAdh. | |||||||||
17 | ID | Adh.% | ||||||||||||
18 | -------- | Adherence | -- | ------- | 24573 | 0.23 | ||||||||
19 | Sched | uled | Scheduled | Actual | Min.In | Min.Out | P | erc.In | 24731 | 1 | ||||
20 | Activ | ities | Time | Time | Adhere | Adhere | Adhere | 27428 | #N/A | |||||
21 | ----- | --------------- | ---------- | ---------- | -------- | -------- | ------ | 21212 | 0.83 | |||||
22 | Sign- | on | 8:00 | 7:27 | 424 | 56 | 88% | 25966 | 0.9 | |||||
23 | Sign- | off | 0:00 | 0:20 | 0 | 0 | 0% | 28137 | #N/A | |||||
24 | Lunch | 0:30 | 0:30 | 7 | 23 | 23% | 5598 | 1 | ||||||
25 | Perso | nal | 0:00 | 0:04 | 0 | 0 | 0% | 11950 | 0 | |||||
26 | Idle | w/oReason | 0:00 | 0:09 | 0 | 0 | 0% | 26908 | #N/A | |||||
27 | ===== | =============== | ========== | ========== | ======== | ======== | ====== | 4372 | #N/A | |||||
28 | Total | 8:30 | 8:30 | 431 | 79 | 85% | 25860 | #N/A | ||||||
29 | 24200 | 0 | ||||||||||||
Sheet1 |
If the employe above is missing the "lunch" line item...the forula keeps looking until it finds "lunch" further down the sheet.
Here is the entire formula from column "L" - I then do a simple lookup from another sheet to get the scores per person.
=INDEX($H$1:$H$3000,MIN(IF($A$1:$A$3000="lunch",IF(ROW($H$1:$H$3000)-ROW($H$1)+1>MATCH($N18,$A$1:$A$3000,0),ROW($H$1:$H$3000)-ROW($H$1)+1,""))))