• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup by month

T

topbink

New Member
I have a list of transactions named
Code: 
TrxTable as follows

[pre][code]TrxTable

Date       B/S   Desc    Qty   Price   Total

05 Jan '12  Buy      A     100    100    10000
12 Jan '12  Buy      A      50    100     5000
02 Feb '12  Buy      B      50    105     5250
16 Feb '12  Buy      A      50    110     5500
10 Mar '12  Sell     A    -100    100   -10000
17 Mar '12  Sell     B     -50    105    -5250
and I need to get the [code]price on the table above to match the month
(and year) and item desc[/code] set from the table below:

PriceTable

Month Price_A Price_B
Jan 12 100 110
Feb 12 110 105
Mar 12 100 105[/code][/pre]
Can ms excel do that? I'm using MS Excel 2010. Thanks for the help people!
 
Topbink


Try something like:

=Sumproduct((Date Range>=1/1/12)*(Date Range<=31/1/12)*(Desc Col="A/B"), Price Column)

Adjust accordingly
 
Thanks for the response Hui,


My apologies for not saying it correctly, but I don't quite understand how to adjust the sumproduct formula you gave.

What I meant with the case was the price data in
Code: 
TrxTable taken from [code]PriceTable. So when a transaction of product A recorded on 12 Jan '12 for example, the price in TrxTable
will look for the price matched to the month in PriceTable[/code] (100).


:) :)
 
Hi ,


Try this , entered as an array formula , using CTRL SHIFT ENTER :


=INDEX(IF(C2="A",$L$2:$L$4,$M$2:$M$4),MAX((DATEVALUE(LEFT(A2,2)&"-"&MID(A2,4,3)&"-"&RIGHT(A2,2))>=$K$2:$K$4)*ROW($K$2:$K$4)-ROW($K$2)+1))


This assumes the following :


1. Your Price Table is in the range K1:M4 , with the headers in row 1.


2. Your transaction table is in the range A1:D7 , with the headers in row 1.


3. The only possibilities in column C are "A" and "B".


4. The dates entered in column A , are all text , in the format "dd mmm 'yy".


5. I have modified the dates in your price table to refer to the beginning of each month , so that the dates are 1/1/2012 , 2/1/2012 and 3/1/2012 referring to Jan-1 , Feb-1 and Mar-1.


Narayan
 
Hi Topbink


You can use Index - Match combination to get the answer..


I have copied the data from your query as shown below in a single sheet.

A, B, C, etc till J are the column headers as seen in the excel while 1, 2, 3 etc are the row headers.

So "Date" is in cell A1.

Column G is blank.

Your Price Table is from column H to Column J. (here i have taken it in the same sheet, though this can be maintained in another sheet so that you can add different products like C, D, etc).


So if i understand correctly, the exercise here is --

> Date, B/S, Desc & Qty is entered manually

> Total is calculated by Qty * Price

> We need to get the Price from the price table by matching Date (to get the relevant month) and the product.


A B C D E F G H I J

1 Date B/S Desc Qty Price Total Month A B

2 05-Jan-12 Buy A 100 100 10000 Jan'12 100 110

3 12-Jan-12 Buy A 50 100 5000 Feb'12 110 105

4 02-Feb-12 Buy B 50 105 5250 Mar'12 100 105

5 16-Feb-12 Buy A 50 110 5500

6 10-Mar-12 Sell A -100 100 -10000

7 17-Mar-12 Sell B -50 105 -5250


Input the below formula in cell E2 to get the answer.


=INDEX($H$1:$J$4,MATCH(TEXT($A2,"mmm'yy"),$H$1:$H$4,0),MATCH($C2,$H$1:$J$1,0))


How does this work...


Index formula Returns a value or the reference to a value from within a table or range.


The syntax is INDEX(array, row_num, [column_num])


Array: $H$1:$J$4 (this is the price table)


row_num: MATCH(TEXT($A2,"mmm'yy"),$H$1:$H$4,0)

Here you look for the date in column A and use the text funtion to format it as mmm'yy. The lookup array is the column containing the months. 0 (zero) is the match type which is exact match.

This will give the row in which to look for the resultant value.


[column_num]: MATCH($C2,$H$1:$J$1,0)

Similar to above for row number...


Couple points to note:

1. For the price table, i have changed the header from Price_A & Price_B to A & B to match the products in the TrxTable. Due to this change, we can get the column number for the index funtion.

2. If the price table is in another sheet, then it is much better (if columns in either table are going to increase).

3. you can think of using named ranges in place of ranges used in the formula.
 
Hi,


A simple way to do this would be to add a column at the end of your TrxTable with an IF formula

=IF(C3="A",1,2)

This column can then be hidden.


In the price column, use the formula

=INDEX($A$14:$C$16,MATCH(DATE(YEAR(A3),MONTH(A3),1),$A$14:$A$16,0),G3+1)


This method gives you more flexibility. If you have a third category C, it will be pretty easy to modify this formula to get the desired result.


Note: I've assumed that all the dates have been entered as dates, not as text.


- The Excelphile
 
Hi Everybody,


@Majay, Narayank, theExcelphile, and Hui just realized how easy INDEX-MATCH is...


I take Majay notes using named ranges in the table and formula and yes, I use separate sheet for price table thats makes it easier.


Thank you very kindly everybody, I know I can count on this forum to save my day :):)


Cheers,

topbink
 
You must log in or register to reply here.
Back
Top