VLookUp or other function to return matched data from multiple col

[MMangen]

I have raw data that I put into a spreadsheet that I need to match to a
pre-existing "legend".

Legend Set-Up:
Column A - Storefront Name
Column B - Publisher Name
Column C - Placement Name
Column D - Orders

I need to automatically insert data into Column D (Orders) by searching AND
matching (exactly) all three Columns A-C in the "raw data" tab.

I realize that VLOOKUP will only search the first Column in my Legends tab.

Now I am at a complete loss how to achieve the desired outcome.

Any ideas on how to do this would be really appreciated!

 

[NBVC]

Try


Code:

 

[Luke M]

You can use SUMPRODUCT

If returning numbers:
=SUMPRODUCT(('Raw Data'!A$2:A$100=A2)*('Raw Data'!B$2:B$100=B2)*('Raw
Data'!C$2:C$100)*('Raw Data'!D$2$100))

If you're trying to return text:
=INDEX('Raw Data'!D,SUMPRODUCT(('Raw Data'!A$2:A$100=A2)*('Raw
Data'!B$2:B$100=B2)*('Raw Data'!C$2:C$100)*(ROW('Raw Data'!A$2:A$100))))

Adjust range sizes as appropriate. Note that you can't callout the entire
column inside the SUMPRODUCT function unless you have XL 2007.

 

[Shane Devenshire]

Hi,

What exactly are you trying to return? Is it numeric data or text? Do you
want to count or sum the data? Can there be more than one item that matches
all three conditions?

Here is an example:

=SUMPRODUCT(--(A$2:A$16>=M1),--(B$2:B$16=M2),--(C$2:C$16=M3),D216)

 

[MMangen]

Wow, intense! Okay, I'm wondering if something is still off as I'm getting a
#N/A and there is data in the referenced column (unless a reference is off).

Should any part of that formula reference the Legend Tab?

Also, if it's easier maybe I can call you? Whatever works!

 

[MMangen]

Luke:

Thank you so much for taking your time to respond to my question. I am using
Excel 2007 and trying to return a number.

I may not have been entirely clear in my original question (I am getting a
Value error right now):

In the Legend Tab I need to have the number pulled from the Raw tab if
Columns A-C have an identical match in the Raw tab. The number that needs to
be retrieved is in the Raw tab (needs to be inserted into Column D in the
Legend Tab.

So for example:
In Legend Tab:
Column A - red
Column B - blue
Column C - yellow

Then I need for the formula to go search in Raw tab to see the number of
items that match all three of the above referenced criteria - in this case 9.

Hope this helps more.

Michelle

 

[NBVC]

I tested it and it worked... If you are in the Legends tab, you don't
need to include that name in the formula...

Make sure each value in A2, B2, C2 match exactly to the raw data column
A, B and C values.. (check for spaces, spelling, format)...

Also, make sure your ranges in the formula are large enough to cover
all raw day rows.

 

[MMangen]

Hi Shane:

Thank you so much for your time with this!

It is numeric data I am trying to return. (right now the result is showing
as zero). Do I need to change the --- in your formula below to the actual tab
name? (with appropriate '! and all)

No, there will not be more than one time that would match all three
conditions. In some cases there will be no third condition, only two.
(meaning the Column C in Raw Data would be blank).

In the Legend Tab I need to have the number pulled from the Raw tab if
Columns A-C have an identical match in the Raw tab. The number that needs to
be retrieved is in the Raw tab (needs to be inserted into Column D in the
Legend Tab.

So for example:
In Legend Tab:
Column A - red
Column B - blue
Column C - yellow

Then I need for the formula to go search in Raw tab to see the number of
items that match all three of the above referenced criteria - in this case 9.

Hope this helps more.

 

[NBVC]

Note: My formula only returns the first occurance of what might be in
column D of the "Raw" tab...

Use Sumproduct to get a count of matching entries...

=SUMPRODUCT(--('Raw Data'!A$2:A$100=A2),--('Raw
Data'!B$2:B$100=B2),--('Raw

Data'!C$2:C$100))

Note that you have to adjust ranges to suit your raw data.. make sure
the sheetname in the formula matches actual sheetname and that matches
are exact in the 3 columns to your variables.. again check spelling,
spaces, etc.. and that there are no errors in any of those columns
already....

 

[MMangen]

Thanks again, I did double check everything and everything is identical
(copied from one to the other for super duper certainty!) in Columns A-C on
both tabs. Still no success.

Michelle

 

[NBVC]

Review Post #8 above.

 

[MMangen]

Luke:

Actually, I was wrong, this formula is working EXCEPT I found there are
instances of multiple lines having matching criteria and the formula isn't
picking up all instances.

So: for example: if there are three lines that all say Luke and you ordered
one item three separate times. It only tells me you ordered one, instead of
three.

Any ideas on how to get a formula to pick up that last part?

 

[NBVC]

Again, see post 8 above...

SUMPRODUCT syntax here:

=Sumproduct(--(CriteriaRange1=Criteria1),--(CriteriaRange2=Criteria2),SumRange)

where you can additional --(CriteriaRangeX=CriteriaX)