Combing Vlookups&If statements - Please Help

[SteveF0001]

Hi All,

I am trying to build a formula that will search one column for a match then go on to search a second column to find the appropriate match, but on the second search I am picking up the first qty, not the correct one.

Data would be
If store 3, Widget B, Qty = 3

Store List Widget Qty
1 A 6
2 A 3
3 A 1
1 B 4
2 B 9
3 B 3
1 C 7
2 C 9
3 C 11

Can anyone help?

Thanks!

 

[SkipVought]

Hi,

1. Use Named Ranges based on your column headings

2. The formula
[tt]
=SUMPRODUCT((Store_List=E1)*(Widget=F1)*(Qty))
[/tt]
where E1 contains the Store List criteria and F1 contains the Widget criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

If you're using Excel 2007, there is a new function available called SumIfs. It is like SumIf but allows you to use multiple criteria.

The formula would be:
[tab]=SumIfs(C:C, A:A, 3, B:B, "B")

The "3" and ""B"" could be replaced with cell references if you want to be able to change filters on the fly.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[lionelhill]

... of course if you are ever obliged to return the contents of a cell in column C based on selection criteria in columns A and B, but the data in C are NOT numerical, you can make a (hidden?) helper column =A&B or =concatenate(A,B) and then do vlookup using the helper column.