Results 1 to 6 of 6
Thread: vlookup and match (xp)

20070605, 06:43 #1
 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..

20070605, 06:53 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20070605, 07:48 #3
 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>

20070605, 07:54 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20070605, 08:31 #5
 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..

20070607, 06:14 #6
 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