lookup problem.
 

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.

lookup problem.
 

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.

lookup problem.
 

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.

lookup problem.
 

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.