Excel - vlookup for multiple criteria? 2

[Allilue]

Hello,

Is there a way to do a vlookup with multiple criteria? i have 2 columns that i need to lookup, and the setup is the same for the lookup table. if vlookup does not enable this, can it work with a sumif? i tried to select both columns, but it doesn't work. then i tried to place an "AND" statement within the lookup, and that doesn't seem to work either... any suggestions?

thanks...

 

[ilses]

Allilue,

If you want to sum over two categories, you could use a SUM array formula. See here. A better explanation will be hard to find!

IS

 

[JVFriederick]

Check out the FAQ "Summarize data with a single equation"

If you can work through the example it's well worth it for what you're trying to do.

 

[Allilue]

I'm really confused now...

I'll try to explain this in more detail.
I have a spreadsheet with 3 columns:

1)Name
2)Product
3)%

Then I have another spreadsheet with Name and Product (this list contains only some of the Names in the 1st sheet). I want to look up the % if the Name equals the Name in the 1st sheet AND if the Product equals the Product in the 1st sheet. I can't seem to figure this out. I tried combining functions but these don't return a value.

help

 

[Allilue]

JVFrederick,

OK, I read the FAQ and that looks like what I need, thanks!

Now, when I actually tried it, I set it up exactly as you had it shown. The only difference was that the variables were on a different sheet. But I figure that shouldn't make a difference. The only other thing different is that the numbers I am trying to sum are percentages. Will this affect the output? I still get #Value.

 

[JVFriederick]

It will still work, just be sure the cell references on sheet #2 point to sheet #1. I like to name sheet #1 "A" while doing this sort of stuff - keeps the formulas easier to read. I change the sheet name later after all is working OK.

The cell display cell should look something like this :
{=SUM((A!$B$11:$B$41="A&quot*A!F$11:F$41)}

If you do not see { } at the begin and end, the formula has not been entered with Ctrl+Shift+Enter. Press F2 key while on the cell, then press Ctrl+Shift+Enter at the same time.

I've noticed that the first cell within each formula reference needs to have something there. Doesn't work if the first row has a blank. (In other words, cell B11 and F11 need to be non-blank)

If you are still having trouble, send the file to me at JVFriederick@Yahoo.com and I will fix it for you.

 

[DarkSun]

Have you tried the Excel Addin.

Go to Tools-Addins, then select "Conditional Sum Wizard".

This will then create an menu item under Tools-Wizard. Select Conditional Sum then go through the wizard.

 

[JVFriederick]

Have we lost our author ?

 

[Allilue]

I'm still here! Sorry, just checked now...

I think that is the answer to my problem! THe part about the cells having to be non-blank. Many of my cells are blank, but I guess I can just format it so that they are zero instead. Let me try it and I'll let you know...

THanks!

 

[JVFriederick]

Only the cells in the first row must be "non-blank".

 

[Allilue]

Oh no....It didn't work...I'll try again, but if I can't figure it out, I may have to send you an example. I only made the first cells non-blank (as well as the last..just in case!).

Wish me luck!
)

 

[JVFriederick]

Go ahead and send it to JVFriederick@Yahoo.com
I'll look at it this afternoon

 

[Allilue]

Ok, I just found something out...

I have my lookup list on Sheet 1. That list contains 2 columns of data to look up. But the first column's data repeats itself many times:

AAA
BBB
CCC
AAA
BBB
CCC

When I get rid of the repetition, it works. But I need it in there since the second column is different. But each pair of columns are unique:

AAA 111
BBB 111
CCC 111
AAA 222
BBB 222
CCC 222

Is this not working because the first column has repeating values? If so, how can I overcomet his problem?

 

[JVFriederick]

No, repeating values are perfectly fine.
Be happy to look at the file . . . . .

 

[Allilue]

Well,

Believe it or not, it finally worked when I filled in ALL of the cells in the columns so they were not blank. But the strange thing is, I tried it on another workbook and it worked with blank cells. I'm thinking maybe it has something to do with the size of my ranges. I have 1,396 rows in my columns, so maybe this is affecting it?

But anyways, it works...the only thing is that I wanted to have that number calculated in a cell, and multiply another number to it. But I got a message "cannot change part of an array". Any way around this?

 

[JVFriederick]

As far as the last question, just edit the array with F2 key, then add the math to multiply at the end. Now press Ctrl+Shift+Enter and it will look something like this :

{=SUM((A!$B$11:$B$41="A&quot*A!F$11:F$41)*$A$8}

I'm guessing that you had cells with spaces in certain columns when the array was looking for numbers. If the first row has a number, it wants a number for every row in the column.

Hope it worked well for you.

 

[Allilue]

Yes, it's fine now! Thanks!
It took a little while to fill in the gaps, but it works now... )

 

[JVFriederick]

Glad to hear it.