Look for a value that might be in many columns

Question

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

Answer 1

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

Answer 2

• you can construct a virtual table from columns you want:

  =IFERROR(VLOOKUP($A2, {'ac6'!$D:$D, 'ac6'!$L:$L;
                         'ac6'!$F:$F, 'ac6'!$L:$L;
                         'ac6'!$H:$H, 'ac6'!$L:$L}, 2, 0), 0)

  =ARRAYFORMULA(IFERROR(VLOOKUP($A2:$A, {'ac6'!$D:$D, 'ac6'!$L:$L;
                                         'ac6'!$F:$F, 'ac6'!$L:$L;
                                         'ac6'!$H:$H, 'ac6'!$L:$L}, 2, 0), 0)

Answer 3

I like sancho's SUMPRODUCT approach using * as AND but I would simplify the boolean OR to the addition symbol +.

So, the logic based on the order of operations in math (eg. PEDMAS), requires placing the 3 OR conditions within parentheses, =(A+B+C)*D.

Simplified Formula

 =SUMPRODUCT(
    ((A2=ac6!D:D)+(A2=ac6!F:F)+
     (A2=ac6!H:H))*(ac6!L:L))