VLOOKUP inside array formula

[dipique]

Hi! The attached excel file contains some sample data and a fairly detailed explanation of the problem. I've put it here as well, but it's not as pretty. Thanks in advance for any help you can give... I'm hoping there's a simple solution. Thanks!

Here goes:


SKU Cost
5 10 <--This represents a vendor invoice
4 5
3 6
1 2
1 3
2 4
2 10

------------

SKUs List Cost
1 2 <--This table tells me how much each SKU is supposed to cost
2 4
3 6
4 8
5 10

------------

I need formulas that come up with these two values:

# of SKUs Overbilled: 2
$ Overbilled: 7

Note: One of the lines was underbilled, but that's okay. I like being underbilled.

-------------

Each formula needs to do all the work--no helper cells or columns. I'll be using indirect formulas to pull this data in from multiple sheets for multiple vendors,
and the people maintaining the sheet can't handle much more than copying a tab and giving it the right name.

<In the spreadsheet, I include an example here of how I would do this if I could use helper columns. These result in two cells that contain the correct values, as shown above.>

-------------

So the question is: how can I use a combination of array/match/index/vlookup/sumproduct/whatever to put this work into a single cell that can reside on a separate sheet and can be strung together using indirect?

Please take a look at the spreadsheet! I color-coded it so it's a little easier to read than this. Thanks in advance,

Dan

[Ace_XL]

In C13
=SUMPRODUCT(COUNTIFS($I$2:$I$6,$A$2:$A$8,$J$2:$J$6,"<"&$B$2:$B$8))

In C14
=SUMPRODUCT(COUNTIFS($I$2:$I$6,$A2:$A8,$J$2:$J$6,"<"&$B2:$B8),$B$2:$B$8)-SUMPRODUCT(COUNTIFS($A$2:$A$8,$I2:$I6,$B2:$B8,">"&$J$2:$J$6),$J$2:$J$6)

[dipique]

Frickin brilliant. Reputation added, marking as solved.

Dan

[Teethless mama]

Try this one. short/elegant solultion

C13: SUMPRODUCT(--(LOOKUP(A2:A8,I2:J6)<B2:B8))

C14: =SUMPRODUCT((B2:B8>LOOKUP(A2:A8,I2:J6))*(B2:B8-LOOKUP(A2:A8,I2:J6)))

[dipique]

I like that. Is there any danger to using LOOKUP (which Excel says is only there for backward compatibility)? I tried using MATCH which I assume is its modern counterpart but it doesn't work.

[Teethless mama]

I like that. Is there any danger to using LOOKUP (which Excel says is only there for backward compatibility)? I tried using MATCH which I assume is its modern counterpart but it doesn't work.

No danger at all. I don't know which text book are you referring to.
MATCH is modern counterpart. <FALSE>

MATCH & LOOKUP function they don't work the same way. Every function it has it own unique way.

[dipique]

Cool. Count me in--I'm using it!