Dynamic searching for specific text within range

CM214

New Member
Joined
May 12, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • RoadwayNetwork.JPG
    RoadwayNetwork.JPG
    146.2 KB · Views: 9
  • Volumes.JPG
    Volumes.JPG
    69.3 KB · Views: 9

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Forum statistics

Threads
1,144,679
Messages
5,725,747
Members
422,636
Latest member
GatorsBucs

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top