Hi all! I'm struggling to create a formula in Excel for a report on potential missed booking opportunities with agents who have a past due balance greater than 90 days. I have Raw Data (Aging Report) and Registered Orders (Origin & Destination) sheets.
I am trying to match cities in Raw Data with cities on the Registered Orders sheet to see if we booked shipments with other agents that operate in the same location as the agent with a past due balance. I need a formula that: Matches city names
Returns the booked agent(s) if a match is found (the formula must return multiple agents)
Handles cases where no match is found
My current formula (shared in the file below) seems to just return all origin agents even for cities where there is no match. I know the issue lies with the inclusion of <>"" at the end of the formula, but if I remove that part then I get No Bookings for all lines. My formula is below:
=IFERROR(TEXTJOIN(",",TRUE,FILTER('Registered Orders by COS - Orig'!A:A,LOWER('Registered Orders by COS - Orig'!C:C)=LOWER('Raw Data'!R2)&'Registered Orders by COS - Orig'!A:A<>"")),"No Booking")
The city names are text in all sheets and have the same capitalization with no leading or ending spaces.
Any help or alternative formulas to achieve this would be greatly appreciated! Thanks!
V1 - Index & Match - Copy.xlsx
Bookmarks