#1
|
|||
|
|||
Multiple worksheet lookup
Hello All,
I have two worksheets that I need a formula or a function to merge data from both worksheets into a third. What I am trying to do is merge data associated with a specific structure (e.g. a bridge); for example in the worksheet called Merged Sheets I have highlighted two examples (also highlighted in other worksheets) of what I am trying to achieve: • Data sourced from the BARRIER_RCS worksheet will be a straight lookup (so I am OK with this). • For a unique Plan Number use the specific ROAD NO and specific RRD (Road Running Distance) to find the road number in the CURR_LANE_WIDTHS worksheet then find where the exact structures RRD sits within the START RRD and END RRD and return the various Lane Widths. BARRIER_RCS This worksheet contains data relating to a range of structures. • Column A contains the plan number of a structure on a road (unique ID). • Column B is the Road Number that the structure is on (there can be multiple structures on a road). • Column C has the Road Running Distance (RRD) location of that structure (each structure will have a unique RRD). CURR_LANE_WIDTHS This worksheet contains physical data related to a variety of roads, i.e. • Column A has the Road Number (note: a road number can be repeated multiple times). • Column B has a Start RRD for a variety of road data between two RRD’s. • Column C has the End RRD for each segment of road. • Column D has the road Lane Width of the left lane. • Column E has the road Lane Width of the inner left lane (if the road has two lane travelling in the same direction). • Column F has the road lane width on the right lane. • Column G has the road Lane Width of the inner right lane (if the road has two lane travelling in the same direction). I hope this all makes sense, but if not please ask me to clarify. Regards, Dave T |
#2
|
||||
|
||||
Try Array-Entering (Ctrl+Shift+Enter, not just Enter) this formula in cell D2 of sheet Merged Sheets. Copy down and across.
Code:
=INDEX(CURR_LANE_WIDTHS!D$2:D$70,MIN(IF(((CURR_LANE_WIDTHS!$A$2:$A$70=$B2)*(CURR_LANE_WIDTHS!$B$2:$B$70<=$C2)*(CURR_LANE_WIDTHS!$C$2:$C$70>=$C2))>0,ROW(CURR_LANE_WIDTHS!$A$2:$A$70)))) |
#3
|
|||
|
|||
Hello p45cal,
I really, really appreciate your reply. I had found a post on Ozgrid that was most likely another solution: https://www.ozgrid.com/forum/forum/h...ss-than-checks But I was getting results that did not appear to be quite correct. I also found problems when further researching the SUMPRODUCT formula: - the formula needed to be on the same BARRIER_RCS worksheet and not the Merged Sheets worksheet. - that column lengths for the ranges needed to be the same Here is the formula I modified based on the formula in the attached worksheet from the Ozgrid site. Code:
=IF(SUMPRODUCT(($B$2:$B$1015=$B145)*($C145>=CURR_LANE_WIDTHS!$B$2:$B$1015)*($C145<=CURR_LANE_WIDTHS!$C$2:$C$1015),ROW(CURR_LANE_WIDTHS!$D$2:$D$1015))=0,"",INDIRECT("CURR_LANE_WIDTHS!D"&SUMPRODUCT(($B$2:$B$1015=$B145)*($C145>=CURR_LANE_WIDTHS!$B$2:$B$1015)*($C145<=CURR_LANE_WIDTHS!$C$2:$C$1015),ROW(CURR_LANE_WIDTHS!$D$2:$D$1015)))) I also played with the array formula posted by mikerickson but could not get it to work. I must admit when I looked at the formula from the Ozgrid post I did not expect a reply to this post, so I really do appreciate your reply. Regards, Dave T |
#4
|
||||
|
||||
The Ozgrid site appears down at the moment so I can't comment.
However: Does my offering give you the results you're looking for? |
#5
|
|||
|
|||
Hello p45cal,
Sorry about the delay in getting back to you, but I thought I would re-run both options on a larger data set to see both options and compare the results. The worksheet called Merged Sheets has your array formula in columns D to G. The worksheet called BARRIER_RCS has the formula from the Ozgrid site. In both worksheets I added data in columns J to P, that was copied from the CURR_LANE_WIDTHS worksheet to check the formula results against the expected results. To ensure I had pasted the correct data I added a formula in column I, where 'In range' shows that the actual RRD is between the Start RRD and End RRD distances. Unless I have made a mistake with both formulas you can see they do not match up. It is looking like I will have to ask for one worksheet with all the values rather than do a complicated lookup. Thanks for your help anyway. Regards, Dave T |
#6
|
||||
|
||||
my apologies, it was one row out.
Code:
=INDEX(CURR_LANE_WIDTHS!D$2:D$1015,MIN(IF(((CURR_LANE_WIDTHS!$A$2:$A$1015=$B2)*(CURR_LANE_WIDTHS!$B$2:$B$1015<=$C2)*(CURR_LANE_WIDTHS!$C$2:$C$1015>=$C2))>0,ROW(CURR_LANE_WIDTHS!$A$2:$A$1015)))-1) Where there are errors, you can IFERROR those out. |
#7
|
|||
|
|||
Hello p45cal,
Just tried you addition to the formula and all is good. I really do appreciate you time and effort. Regards, Dave T |
#8
|
|||
|
|||
Hello All,
I was showing a work colleague the formula from the Ozgrid post (in my second attachment) and he found out I had made a mistake. The formula in columns D to G on the BARRIER_RCS worksheet should have been: Code:
=IF(SUMPRODUCT((CURR_LANE_WIDTHS!$A$2:$A$1015=$B2)*($C2>=CURR_LANE_WIDTHS!$B$2:$B$1015)*($C2<=CURR_LANE_WIDTHS!$C$2:$C$1015),ROW(CURR_LANE_WIDTHS!$D$2:$D$1015))=0,"",INDIRECT("CURR_LANE_WIDTHS!D"&SUMPRODUCT((CURR_LANE_WIDTHS!$A$2:$A$1015=$B2)*($C2>=CURR_LANE_WIDTHS!$B$2:$B$1015)*($C2<=CURR_LANE_WIDTHS!$C$2:$C$1015),ROW(CURR_LANE_WIDTHS!$D$2:$D$1015)))) Just thought I would post this followup as it is another alternative that is not an array formula. Regards, Dave T |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple sheet lookup | kevexcel | Excel | 0 | 06-27-2018 12:51 AM |
Sum and Lookup multiple values | coolkat | Excel | 1 | 11-06-2017 03:47 AM |
Match multiple lookup values? | tinfanide | Excel | 3 | 07-02-2017 05:35 AM |
Formula help please (lookup across multiple sheets) | froggybsb03 | Excel | 2 | 03-16-2017 02:16 AM |
How can I retreive multiple records using a lookup? | jrpey | Excel | 3 | 11-14-2011 02:10 PM |