Google-sheets – Look for a value that might be in many columns

formulasgoogle sheetsgoogle-sheets-arrayformulavlookup

Say I have a value in $A, to match to another sheet of the same file. That value (in this case, a student id) might appear in many columns of the other sheet.

In the following example, I am looking for the ID in $A2, and trying to match it to the value in columns D, F or H (all of the IDs on a work done in groups of three) then get back the grade (column L)

iferror(vlookup($A2,'ac6'!$D:$L,9,false),iferror(vlookup($A2,'ac6'!$F:$L,7,false),iferror(vlookup($A2,'ac6'!$H:$L,5,false),0)))

Is there a better way? Right now this is cumbersome enough that I feel the need to use a script to generate these formulas

Best Answer

You could exploit the powers of SUMPRODUCT, much beyond lookups. Once you get used to it, it is fantastic. For your entertainment, I would boast of some examples below...

Try the following formula instead: =SUMPRODUCT((1-(1-($A2='ac6'!$D:$D))*(1-($A2='ac6'!$F:$F))*(1-($A2='ac6'!$H:$H)))*('ac6'!$L:$L)).

The trick of using (1 - (1 - cond1) * (1 - cond2) * ...) is to have logical OR behaviour among conditions. Using (cond1) * (cond2) * ...) gives you logical AND behaviour. And you could perform any logical computation by mixing these.

See

https://stackoverflow.com/a/27780657/2707864

https://stackoverflow.com/a/30854706/2707864

https://stackoverflow.com/questions/21576595/searching-an-excel-with-two-search-words-multiple-lookup-using-vba-macro/21582535#21582535

https://stackoverflow.com/a/45669294/2707864

https://stackoverflow.com/a/48413171/2707864

Short answer

The desired result could be obtained without scripting and this is recommended for cases where number of cells is small, especially if the OP don't know about scripting. To do so,

Add auxiliary columns for each sorting criteria
Then use the SORT() or QUERY() functions.

Extended answer

Google Sheets has some additional functions regarding those available in other spreadsheet applications. Two of them are SORT() and QUERY() which allow to get the desired result without scripting.

As QUERY() return a blank cell for those containing ? as value, the SORT() function will be used.

Original data

Assume that the original data is in A2:D13.

Auxiliary columns

Auxiliary columns will be used for sorting. Only one aggregate function is presented in order to keep the answer short.

Add the following formulas and fill down until the last row of the source data.

D2: =Row(C2)-Row($C$2)+1
E2: =ISERROR(LOOKUP("?",OFFSET($A$2,MATCH(A2,$A$2:$A$13,0)-1,2,COUNTIF($A$2:$A$13,A2))))
F2: =AVERAGEIF($A$2:$A$13,A2,$C$2:$C$13)

If you find helpful having column headers, add them to the row 1.

Key Formula

G2: =ARRAY_CONSTRAIN(SORT(A2:F13,5,TRUE(),6,TRUE(),1,TRUE()),12,3)

ARRAY_CONSTRAIN is included to limit the result to the columns of the source data.

Google Sheets – Search Text in All Columns of Multiple Sheets

You need search (case-insensitive) instead of find (case-sensitive). Also, a few wrappers around it:

=max(arrayformula(iferror(search(A1, sheet1!A1:A1000)))) > 0

iferror ensures empty output instead of #N/A when the cell is empty or text cannot be found
arrayformula specifies that we work with an array
max returns the maximum (so it will be positive if at least one cell matched)
Comparison >0 will return TRUE or FALSE; no if statement needed.

The above works for multiple columns at once: you can use sheet1!A1:Z1000 and so on.

The results from multiple sheets can be pooled together by addition prior to comparison:

=max(arrayformula(iferror(search(A1, sheet1!A1:Z1000)))) + max(arrayformula(iferror(search(A1, sheet2!A1:Z1000)))) > 0