try sumproduct
if your lookup value data is numeric values and there are no blank cells.
Blank cells or text are read as "0"

=SUMPRODUCT(--(Sheet1!A1:E3=A1),(Sheet1!B1:F3))

good luck and regards
--
*****
birds of the same feather flock together..



"MyFairLady" wrote:

Thanks Bernard, but that is too detailed. Is there another way to write it so
that it looks at the range from Sheet2!A1:F3 and return adjacent cell if A1
in Sheet1! matches? I only showed you a partial list but Sheet2! continues
on for quite a few columns

"Bernard Liengme" wrote:

Long and ugly:
=IF(ISNA(VLOOKUP(A1,Sheet2!A1:B3,2,FALSE)),0,VLOOK UP(A1,Sheet2!A1:B3,2,FALSE)
)+IF(ISNA(VLOOKUP(A1,Sheet2!C1:D3,2,FALSE)),0,VLOO KUP(A1,Sheet2!C1:D3,2,FALSE)
)+IF(ISNA(VLOOKUP(A1,Sheet2!E1:F3,2,FALSE)),0,VLOO KUP(A1,Sheet2!E1:F3,2,FALSE))

If the second column is text use
=IF(ISNA(VLOOKUP(A1,Sheet2!A1:B3,2,FALSE)),"",VLOO KUP(A1,Sheet2!A1:B3,2,FALSE)
)& .......

best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MyFairLady" wrote in message
...
I am trying to lookup a column from one worksheet and match it with anohter
column in another worksheet, then I want it to give me the value in the
adjacent cell.
Here is my example:
worksheet A: cell A1: Bstudio2
worksheet B:
A B C D E F
Astudio1 3 Bstudio1 9 Cstudio1 10
Astudio2 4 Bstudio2 0 Cstudio2 5
Astudio3 8 Bstudio3 2 Cstudio3 6

So if worksheet one (A1) matches either column A,C or E in other worksheet
then return B,D or E. Cannot figure out what combination of VlLOOKUP,
MATCH
and/or INDEX to use to get answer.