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

4
  • 2
    Google Sheets is off-topic on Super User, but on-topic at Web Apps. You might want to ask a moderator to migrate this for you. – fixer1234 May 1 '19 at 6:11
  • Upvoted since the question is well formulated, and it shows previous effort from the OP. Downvoting because of being off-topic might not be the best option, but rather flagging/commenting as done by fixer1234. – sancho.s ReinstateMonicaCellio May 5 '19 at 11:29
  • the downvoting was a bit disorienting until someone said that it was if topic – josinalvo May 5 '19 at 12:00
  • i wish i had known before putting the bounty – josinalvo May 5 '19 at 12:00
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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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