# Vlookup formula again

#### garychaan

##### New Member
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?

Thanks.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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?

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

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?

This message was edited by garychaan on 2002-09-10 23:19

(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

Replies
0
Views
145
Replies
1
Views
253
Replies
0
Views
274
Replies
1
Views
390
Replies
9
Views
629

1,203,263
Messages
6,054,446
Members
444,725
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back