Find value from another workbook, row based on text, column based on date

Hi all,

I'm looking for an Excel Online formula that would be able to return a value from a specific tab (Schedule) from another workbook (Sourcebook.xlsx) that is uploaded to a OneDrive folder ("sharepoint,com/Shared Documents/", had to replace . with , due to post limits) on a cross of two specific values - selecting row based on finding specific text (Search_text) in one column and then selecting column based on current date.

I tried INDIRECT but it works only sometimes, even if I open the source workbook first.
This is how the indirect I used looks:

Formula:

Please Login or Register  to view this content.

Multiple other solutions I tried return an error "Request was taking too long" when trying to parse them. I tried:

• XLOOKUP (instead of VLOOKUP, as below)
• FILTER + CHOOSECOLS
• OFFSET + MATCH

The other solution that I kind of got to work is with VLOOKUP, however, it only finds the first row with the specified text and I need the last one (which is third time that text appears on the list).

Formula:

Please Login or Register  to view this content.

I would be grateful if someone could help me with this by either providing an alternative solution (in form of a formula) or a way to effectively shift the result from VLOOKUP variant by a given number of rows/to a row with specific additional parameter ("resource type" in the example sourcebook).

In the attached Sourcebook.xlsx I have marked in yellow the values that are of interest to me, the value I'm looking for is the one in AD64 cell (cross of the "Search_text" and today's date).

Your dates were not dates. They just looked like dates.

I formatted them as Dec rather than 12 to prove they're real dates.

Then:

=INDEX([Sourcebook.xlsx]Schedule!$C$2:$AG$76,MATCH(1,([Sourcebook.xlsx]Schedule!$A$2:$A$76="Search_Text")*([Sourcebook.xlsx]Schedule!$B$2:$B$76=3),0),MATCH(TODAY(),[Sourcebook.xlsx]Schedule!$C$1:$AG$1,0))

This might be a bit better...

=INDEX(INDEX([Sourcebook.xlsx]Schedule!$C$2:$AG$76,,MATCH(TODAY(),[Sourcebook.xlsx]Schedule!$C$1:$AG$1,0)),MATCH(1,([Sourcebook.xlsx]Schedule!$A$2:$A$76="Search_Text")*([Sourcebook.xlsx]Schedule!$B$2:$B$76=3),0))

Hang on! I made a mistake. In your excel version previous offerings will be array formulae. Try this:

=INDEX(INDEX([Sourcebook.xlsx]Schedule!$C$2:$AG$76,,MATCH(TODAY(),[Sourcebook.xlsx]Schedule!$C$1:$AG$1,0)),MATCH(1,INDEX(([Sourcebook.xlsx]Schedule!$A$2:$A$76="Search_Text")*([Sourcebook.xlsx]Schedule!$B$2:$B$76=3),0),0))

which should be OK with just ENTER.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.