Results 1 to 7 of 7

VLOOKUP inside array formula

Threaded View

  1. #1
    Forum Contributor
    Join Date
    09-16-2009
    Location
    Dallas, TX
    MS-Off Ver
    Office 2013
    Posts
    107

    VLOOKUP inside array formula

    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
    Attached Files Attached Files
    Last edited by dipique; 07-02-2013 at 05:01 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1