lookup problem.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Long and ugly:
=IF(ISNA(VLOOKUP(A1,Sheet2!A1:B3,2,FALSE)),0,VLOOKUP(A1,Sheet2!A1:B3,2,FALSE)
)+IF(ISNA(VLOOKUP(A1,Sheet2!C1:D3,2,FALSE)),0,VLOOKUP(A1,Sheet2!C1:D3,2,FALSE)
)+IF(ISNA(VLOOKUP(A1,Sheet2!E1:F3,2,FALSE)),0,VLOOKUP(A1,Sheet2!E1:F3,2,FALSE))

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

best wishes
 
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
 
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
 
Back
Top