I have a spreadsheet where I automatically get hundreds urls and spreadsheet names (half home address half full names) and put them in the spreadsheet column. I only need around 50 of the urls based on a set of addresses I track.

Each spreadsheet name has the address inside of it that I need to search for.

Somehow I need to search the spreadsheet name column for an address in another cell, and then return the url that is adjacent to the column. Even if it returned both the url column and the spreadsheet name column that would be great.

I've tried a few things, most notably the formula below which has worked for me in the past for similar issues, but this formula doesn't explicitly include searching the cells for the string I am looking for.

=ARRAYFORMULA(IFERROR(A1:A,QUERY('Finding INformation'!A:B,"select A where B contains "A1:A"")))

I created a spreadsheet with fewer values and no sensitive information.

CodePudding user response：

use:

=INDEX(IFNA(VLOOKUP(A1:A, {INDEX(SPLIT('Finding INformation'!B1:B, " /", ),,1), 
 'Finding INformation'!A1:A}, 2, )))