Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2007
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup and match (xp)

    I require to be able to look up a date (using a vlook up) and return the quantity of a few products listed horizontally (I believe a match formula.. maybe??)
    any ideas would be great! thank you..

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup and match (xp)

    Welcome to Woody's Lounge!

    If you have a table setup like this:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>Date</td><td>Apples</td><td>Pears</td><td align=center>2</td><td align=right>01/01/2007</td><td align=right>100</td><td align=right>50</td><td align=center>3</td><td align=right>02/01/2007</td><td align=right>120</td><td align=right>30</td><td align=center>4</td><td align=right>03/01/2007</td><td align=right>110</td><td align=right>35</td><td align=center>5</td><td align=right>04/01/2007</td><td align=right>90</td><td align=right>45</td><td align=center>6</td><td align=right>05/01/2007</td><td align=right>70</td><td align=right>55</td><td align=center>7</td><td align=right>06/01/2007</td><td align=right>105</td><td align=right>40</td></table>
    you can use VLOOKUP, for example

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>10</td><td>Date</td><td align=right>05/01/2007</td><td align=center>11</td><td>Apples</td><td align=right>70</td></table>
    The formula used to look up the quantity for apples is =VLOOKUP(B10,A2:C7,2,FALSE)
    B10 contains the lookup value (the date)
    A2:C7 is the data range.
    2 is the column from which to return the result (to return the quantity of pears, you'd use 3 instead of 2).
    FALSE means that you're looking for an exact match.

  3. #3
    New Lounger
    Join Date
    Jun 2007
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup and match (xp)

    Edited by HansV to present data in table format

    Thanks Hans,
    However is it possible to keep the format as laid out in your e.g 1?

    what I have is data set out like this:

    <table border=1><tr><td align=right>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup and match (xp)

    One option is to use SUMPRODUCT formulas, another is to use a pivot table. Both approaches are demonstrated in the attached workbook.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Jun 2007
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup and match (xp)

    This looks great Hans

    I will take a better look and let you know

    many thanks..

  6. #6
    4 Star Lounger
    Join Date
    Mar 2004
    Location
    Griffith, New South Wales, Australia
    Posts
    507
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup and match (xp)

    Hans,

    I have severely wandered out of my comfort zone - but have found you still handing out wisdom and solutions, so thought I'd ask something else.

    Can the vlookup function return values in more than one column? My example is that I have a spreadsheet that works as a asset register for PCs. But I want to be able to create a worksheet for each site and be able to select all the data (contained in 6 columns) for each PC per site.

    Is that possible?

    Later: No need to answer. I have found what I needed to know. Thank you Later: No need to answer. I have found what I needed to know. Thank you

Posting Permissions

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