# Look for a value that might be in many columns

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

• 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. May 1, 2019 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. May 5, 2019 at 11:29
• the downvoting was a bit disorienting until someone said that it was if topic May 5, 2019 at 12:00
• i wish i had known before putting the bounty May 5, 2019 at 12:00

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

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