0

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

0

3 Answers 3

0

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

0
  • 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)

0

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))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.