I have a dataset shown below that is used for production scheduling. I would like to be able to look up by the bay number and the date and return the job # and the phase that the date falls in. If I reorganize the data, I can use an index match and concatenate function. However, the scheduler would prefer to be able to see the job scheduling horizontally instead of vertically and I am struggling to come up with a formula that works.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Lookup function with multiple criteria
- Thread starter willtways
- Start date
dreid1011
Well-known Member
- Joined
- Jun 4, 2015
- Messages
- 3,085
- Office Version
- 365
- Platform
- Windows
Welcome to the board. See if this does what you want:
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Job # | Bay # | Phase 60 | Phase 65 | Phase 70 | Phase 80 | Phase 90 | ||
| 2 | 8000 | 25 | 1/31/2024 | 2/2/2024 | 2/28/2024 | 2/28/2024 | 3/14/2024 | ||
| 3 | 7999 | 24 | 2/2/2024 | 2/4/2024 | 3/2/2024 | 3/2/2024 | 3/16/2024 | ||
| 4 | 7998 | 5 | 2/10/2024 | 2/12/2024 | 3/10/2024 | 3/10/2024 | 3/24/2024 | ||
| 5 | 7997 | 4 | 2/7/2024 | 2/9/2024 | 3/7/2024 | 3/7/2024 | 3/21/2024 | ||
| 6 | 7996 | 3 | 1/29/2024 | 1/31/2024 | 2/26/2024 | 2/26/2024 | 3/12/2024 | ||
| 7 | |||||||||
| 8 | |||||||||
| 9 | Lookup Values | Return Values | |||||||
| 10 | Bay # | 5 | Job # | 7998 | |||||
| 11 | Date | 2/10/2024 | Phase # | Phase 60 | |||||
Sheet3 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D10 | D10 | =XLOOKUP(B10,B2:B6,A2:A6) |
| D11 | D11 | =XLOOKUP(B11,XLOOKUP(B10,B2:B6,C2:G6),C1:G1,"") |
Upvote
0
Solution
