google sheets
If sheet 1 has company names (and duplicates of those names)
company A
company B
company A
company A
the other sheet has all those company names and their URLs (no duplicates)
company A - url
company B - url
company C - url
Is there a formula to allow Sheet 1 to look at sheet 2, and if there is a match to return the corresponding url next to the name?
The query function does approximately this.
=query(Roster!A:D, "select B, A where D = 'Kavala' order by A", 1)
selects the rows where department is Kavala and takes the entries from B (name) and A (title), sorting by title alphabetically.
To avoid getting a header row from the query, use
=query(Roster!A:D, "select B, A where D = 'Kavala' order by A label B '', A ''", 1)
The only issue remaining is that you want some custom order by title, even with separating rows. The query won't be able to do that but you can use multiple queries for specific ranks:
=query(Roster!A:D, "select B, A where D = 'Kavala' and B = 'Inspector' order by A", 1)
Ultimately there will be a conflict between the desire for automatic transfer of data from one sheet to another, and the manual formatting of the second sheet.
=ARRAYFORMULA(SORT(VLOOKUP(QUERY(
{ROW(B2:B), SORT(A2:B)},
"select max(Col1) where Col2 <> '' group by Col2 label max(Col1)''", 0),
{ROW(B2:B), SORT(A2:B)},
{2, 3},
0), 1, 0))
this kind of creates a new virtual array in a virtual column which sorts that column with dates and with the rest of the data feeds QUERY which is returned in VLOOKUP consisting of columns 2 and 3 (virtual column 1 is omitted) and lastly output gets sorted by column 1 and 0 indicates that sorting is descending (1 would be ascending)
european syntax:
=ARRAYFORMULA(SORT(VLOOKUP(QUERY(
{ROW(B2:B) \ SORT(A2:B)};
"select max(Col1) where Col2 <> '' group by Col2 label max(Col1)''"; 0);
{ROW(B2:B) \ SORT(A2:B)};
{2 \ 3};
0); 1; 0))
Best Answer
Yes, this is what
VLOOKUPdoes: it looks up an entry in a given range, in vertical direction. For example,would be a formula for the second row in Sheet1: it searches column A of the second sheet for whatever name is in A2, and returns the corresponding value in B column of Sheet2.
If some names appear more than once in Sheet1, you'll get the URLs more than once, too.
It looks like you're okay with duplication in Sheet1, but just in case, I'll point out the command
UNIQUEwhich distills a given range to unique values in it.