Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Matching an Array in one Tab with an Array with another Tab
I am trying to match to arrays in two different Excel tabs I will make a
sample of each tab. Tabl A B C 12 150 Class 10 150 No Class Tab2 A B C D 12 150 NoClass 350 10 150 NoClass 400 What I am trying to do, is to Match Columns (A,B,C) from Tab1 to Tab2, and to display the value of Column D. I can get this to work by matching Column A from Tab1 to Tab2 and displaying Column D, using the Vlookup() function. I just can't figure out how to much mutiple columns in a row between tabs. Thanks |
#2
|
|||
|
|||
I suggest you insert a column in your table on the 2nd sheet, between the
existing columns C and D. In that column put the formula =A1&" "&B1&" "&C1 Then you can write your lookup formula something like this =VLOOKUP(A1&" "&B1&" "&C1,Sheet2!C1:D100,2,0) But note that you'll have a problem with this, as your terminology isn't consistent: on Tab1 you show "No Class"; on Tab2 it's "NoClass" (without the space"). I believe you'll have to address and correct that issue before you can get a formula to work. On Mon, 21 Feb 2005 12:31:06 -0800, "LanceMSU" wrote: I am trying to match to arrays in two different Excel tabs I will make a sample of each tab. Tabl A B C 12 150 Class 10 150 No Class Tab2 A B C D 12 150 NoClass 350 10 150 NoClass 400 What I am trying to do, is to Match Columns (A,B,C) from Tab1 to Tab2, and to display the value of Column D. I can get this to work by matching Column A from Tab1 to Tab2 and displaying Column D, using the Vlookup() function. I just can't figure out how to much mutiple columns in a row between tabs. Thanks |
#3
|
|||
|
|||
My text formatting wasn't the issue, I had just made up that array to pose
the question. I typed the equations as you stated, and wasnt able to get it to work. Thank you for your help though. I would appreciate it if anyone had any other suggestions. Thank you "Myrna Larson" wrote: I suggest you insert a column in your table on the 2nd sheet, between the existing columns C and D. In that column put the formula =A1&" "&B1&" "&C1 Then you can write your lookup formula something like this =VLOOKUP(A1&" "&B1&" "&C1,Sheet2!C1:D100,2,0) But note that you'll have a problem with this, as your terminology isn't consistent: on Tab1 you show "No Class"; on Tab2 it's "NoClass" (without the space"). I believe you'll have to address and correct that issue before you can get a formula to work. On Mon, 21 Feb 2005 12:31:06 -0800, "LanceMSU" wrote: I am trying to match to arrays in two different Excel tabs I will make a sample of each tab. Tabl A B C 12 150 Class 10 150 No Class Tab2 A B C D 12 150 NoClass 350 10 150 NoClass 400 What I am trying to do, is to Match Columns (A,B,C) from Tab1 to Tab2, and to display the value of Column D. I can get this to work by matching Column A from Tab1 to Tab2 and displaying Column D, using the Vlookup() function. I just can't figure out how to much mutiple columns in a row between tabs. Thanks |
#4
|
|||
|
|||
One way would be to use a helper column together with VLOOKUP:
Tab1 ColD: =A1&" "&B1&" "&C1 Tab2 ColD: =A1&" "&B1&" "&C1 Tab2 ColE: your values Then use E1: =VLOOKUP(D1,Tab2!$D$1:$E$100,2,0) Second option is to use: =SUMPRODUCT((A1=Tab2!$A$1:$A$100)*(B1=Tab2!$B$1:$B $100)*(C1=Tab2!$C$1:$C$100)*(Tab2!$D$1:$D$100)) The difference would be that VLOOKUP finds the first (one) value. SUMPRODUCT finds all values and sum's them. Other options would be to use INDEX and MATCH. However, I would probably choose the first solution. Ola Sandstrom Note: Myrna's solution should work if it is changed to ...Sheet2!$D$1:$E$100... |
#5
|
|||
|
|||
Which is essentially what I told him, but he says it doesn't work. Of course
it does.... I don't know what he's done wrong. On Mon, 21 Feb 2005 15:05:01 -0800, Ola wrote: One way would be to use a helper column together with VLOOKUP: Tab1 ColD: =A1&" "&B1&" "&C1 Tab2 ColD: =A1&" "&B1&" "&C1 Tab2 ColE: your values Then use E1: =VLOOKUP(D1,Tab2!$D$1:$E$100,2,0) Second option is to use: =SUMPRODUCT((A1=Tab2!$A$1:$A$100)*(B1=Tab2!$B$1:$ B$100)*(C1=Tab2!$C$1:$C$100)*(Tab2!$D$1:$D$100)) The difference would be that VLOOKUP finds the first (one) value. SUMPRODUCT finds all values and sum's them. Other options would be to use INDEX and MATCH. However, I would probably choose the first solution. Ola Sandstrom Note: Myrna's solution should work if it is changed to ...Sheet2!$D$1:$E$100... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Help with array formula | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |