Matching an Array in one Tab with an Array with another Tab

[Guest]

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

 

[Myrna Larson]

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!C1100,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.

 

[Guest]

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

 

[Guest]

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...

 

[Myrna Larson]

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.