Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP problem | Excel Worksheet Functions | |||
LOOKUP function Problem | Excel Worksheet Functions | |||
Lookup problem want 2 or more results | Excel Discussion (Misc queries) | |||
Zeros problem in LOOKUP? | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions |