Vlookup formula again

[garychaan]

I have a listing of account code which are arranged in ascending order.

The last two digits of the account code represent product type such as......

038101 Travelling Expense product (A)
038102 Travelling Expense product (A)
038105 Travelling Expense product (B)
038106 Travelling Expense product (B)
038108 Travelling Expense product (C)
038109 Travelling Expense product (C)

Can Vlookup formula pull/extract travelling expense amount by product type e.g.Product(A)=(038101+038102)for reporting in Financial Statement fro Product(A)?

How to include conditions for Vlookup formula to pull a string of account code which fall within the specified range. Any suggestions?

Any advice would be appreciated.

Thanks.

 

[WillR]

On 2002-09-10 01:28, garychaan wrote:

I have a listing of account code which are arranged in ascending order.

The last two digits of the account code represent product type such as......

038101 Travelling Expense product (A)
038102 Travelling Expense product (A)
038105 Travelling Expense product (B)
038106 Travelling Expense product (B)
038108 Travelling Expense product (C)
038109 Travelling Expense product (C)

Can Vlookup formula pull/extract travelling expense amount by product type e.g.Product(A)=(038101+038102)for reporting in Financial Statement fro Product(A)?

How to include conditions for Vlookup formula to pull a string of account code which fall within the specified range. Any suggestions?

Any advice would be appreciated.

Thanks.

Use SUMPRODUCT would be one way.

Say codes are in col A2:A7
Names in B2:B7
Values in C2:C7

Formula would look something like this where you want to return sum of some values.

=SUMPRODUCT((A2:A7=E5)+(A2:A7=E6),C2:C7)

E5 & E6 contained say 038108 & 038109

HTH

 

[garychaan]

Hi

I have three questions on Vlookup formula.

(1) Using SUMPRODUCT formula to pull a range of account code amount. Is there any such thing as range uplimit (max. no in the range) for the formula to accept?

(2) If there is a break in account sequence code (e.g. 38101-38116, 38119, 38122-38128)
Can SUMPRODUCT formula pull the account code amount in this situation? Do we need to include more conditions here?

(3) In DGET formula, example DGET(A1:C13,3,E1:F2) What's the middle key '3' represent/define in formula in relation to the database?

Your advice please. Thanks alot.
This message was edited by garychaan on 2002-09-10 23:19

 

[PaddyD]

(1) Using SUMPRODUCT formula to pull a range of account code amount. Is there any such thing as range uplimit (max. no in the range) for the formula to accept?

no upper limit as such, but you cannot use whole column references in the formula. Things like A1:A10 are OK, things like A:A are not. Also, the references must be of the same 'size' . e.g. sumproduct((a1:a10=1)*(b1:b10=2)) is OK, sumproduct((a1:a10=1)*(b1:b5=2)) is not.

(2) If there is a break in account sequence code (e.g. 38101-38116, 38119, 38122-38128)
Can SUMPRODUCT formula pull the account code amount in this situation? Do we need to include more conditions here?

Shouldn't be a problem

(3) In DGET formula, example DGET(A1:C13,3,E1:F2) What's the middle key '3' represent/define in formula in relation to the database?

Dget has the form
DGET(database,field,criteria)

so the 3 indicates which column from the database you want things returned. If, e.g., your database was b1:f10, the 3 would indicate the 3rd column in the database - column d in this case