# 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

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

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