Multiple worksheet lookup

Dave T · #3 11-27-2018, 05:25 PM

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))))

For copying the formula across I needed to change the column ID in the formula and the column number in the INDIRECT("CURR_LANE_WIDTHS!D"&SUMPRODUCT part as well.

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

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