I am successfully using the following function to search an external sheet for a match to a Lot#.
=IF(ISNA(INDEX('\\SW10FIL01\File_Server\Manufacturing\Manufacturing Logs\[Product and Supply Lot Number Log - 2022.xlsx]Daily Totals 2024'!D$1:D$15000,MATCH(C4,'\\SW10FIL01\File_Server\Manufacturing\Manufacturing Logs\[Product and Supply Lot Number Log - 2022.xlsx]Daily Totals 2024'!A$1:A$15000,0))),"",INDEX('\\SW10FIL01\File_Server\Manufacturing\Manufacturing Logs\[Product and Supply Lot Number Log - 2022.xlsx]Daily Totals 2024'!D$1:D$15000,MATCH(C4,'\\SW10FIL01\File_Server\Manufacturing\Manufacturing Logs\[Product and Supply Lot Number Log - 2022.xlsx]Daily Totals 2024'!A$1:A$15000,0)))
This works fine. However, I am trying to string together a formula searching the Daily Totals 2024 data sheet and the Daily Totals 2023 sheet, both in the same workbook. Unfortunately I keep failing to string the two formulas together, either ruining the ISNA check or leading to an odd number of IF formulas. I've tried nested If statements and IFS but can seem to make either work. Is what I am trying to accomplish possible? Any advice would be greatly appreciated.
This is a sample of the cells I am inputting data into. Column "Quantity" has the above IF formula in it.
| Date | Part | Lot | Project # | Start date | Exp date | Quantity | Technician |
|---|---|---|---|---|---|---|---|
| 3/26/2024 | 5119.2842S | GBC140AD | GM10-67 | 3/22/2024 | 3/22/2031 | 10 | TIFFANY |
| 3/26/2024 | 675.725S | I06573 | 11/28/2025 | BERNIE | |||
| 3/26/2024 | 365.706CS | GBC139ND | GM06-60 | 3/22/2024 | 3/22/2031 | 126 | MICHELLE |
The second row of data has a different format due to it being initiated in 2023. I am trying to string a nested IF or IFS statement to search both the 2023 and 2024 Daily Totals sheet, both formatted as the example shown below.
| Lot Number | Part Number | Description | Quantity | Initiated By |
|---|---|---|---|---|
| GBC139ND | 365.706CS | Spacer | 126 | DAWN |
| GBC140AD | 5119.2842S | Screw | 10 | CHARLOTTE |
These two are found in the "Product and Supply Lot Number Log" on sheet "Daily Totals 2024" in the external workbook and the quantity reflects on the example above. I am basically trying to mimic the search of the 2024 sheet and string it to also search the "Daily Totals 2023" sheet. I feel like this should be simple in concept but I am struggling to figure it out. I appreciate anyone willing to take the time to lend advice.