Hello,
Currently using Office 365 (Version 2104); traffic engineer trying to edit a workbook I use often for faster analyses.
So I need to come up with a formula to use in one sheet where it commands Excel to search for a specific text or number in a different sheet within the same workbook, and to eventually return values from neighboring cells.
I attached two images of two sheets to explain what I am trying to come up with. First photo shows a roadway network drawn within cells B3:AA65 for 7 roadway intersections; Second photo shows a table for those 7 roadway intersections and the volumes for each movement at each intersection.
I've been trying to create a formula to place in each cell in the table to have Excel first search the intersection, and then record the right movement volume. What makes things harder is that the roadway network changes per project so I need Excel to dynamically search for each intersection every time a new roadway network is drawn. I've tried to use variations of XMATCH, XLOOKUP, etc. to come up with a way but have not had any luck. I do believe that I will need to number the intersections as "#1, #2, #3,...) to avoid Excel finding a random 1 within the cells shaded.
Ex. Table Sheet Cell E4 (NBT for Intersection 1): Find Intersection 1 in roadway network sheet (cell that has a 1 (L9)); grab value 3 columns right and 3 rows down to N11, return value of N11.
Currently using Office 365 (Version 2104); traffic engineer trying to edit a workbook I use often for faster analyses.
So I need to come up with a formula to use in one sheet where it commands Excel to search for a specific text or number in a different sheet within the same workbook, and to eventually return values from neighboring cells.
I attached two images of two sheets to explain what I am trying to come up with. First photo shows a roadway network drawn within cells B3:AA65 for 7 roadway intersections; Second photo shows a table for those 7 roadway intersections and the volumes for each movement at each intersection.
I've been trying to create a formula to place in each cell in the table to have Excel first search the intersection, and then record the right movement volume. What makes things harder is that the roadway network changes per project so I need Excel to dynamically search for each intersection every time a new roadway network is drawn. I've tried to use variations of XMATCH, XLOOKUP, etc. to come up with a way but have not had any luck. I do believe that I will need to number the intersections as "#1, #2, #3,...) to avoid Excel finding a random 1 within the cells shaded.
Ex. Table Sheet Cell E4 (NBT for Intersection 1): Find Intersection 1 in roadway network sheet (cell that has a 1 (L9)); grab value 3 columns right and 3 rows down to N11, return value of N11.
