Working with a cash worksheet that I list deposits and expenditures. Need to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key words (in
some cases multiple key words) to trigger an accounting code number in the
appropriate cell? For instance Account code '3' may include rent, hotel,
housekeeping, utilities etc. Would like the formula to check for any of
those words and put '3' in the cell recording the relevant accounting code.

Thanks

"Gary" wrote:

Working with a cash worksheet that I list deposits and expenditures. Need to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key words (in
some cases multiple key words) to trigger an accounting code number in the
appropriate cell? For instance Account code '3' may include rent, hotel,
housekeeping, utilities etc. Would like the formula to check for any of
those words and put '3' in the cell recording the relevant accounting code.

Hello Gary

A multiple IF statement might do.
if(or(a2="Rent","Housekeeping","Utilities"),3,if(o r(a2="Salaries","Expenses"),2))

This is limited to seven IF statements so that if this is not enough create
a list of deposits and expenitures and put the ac# in the next column and use
a Lookup formula

Regards Peter

Create a 2 column lookup table with all the key words in the left column and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures. Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key words
(in
some cases multiple key words) to trigger an accounting code number in the
appropriate cell? For instance Account code '3' may include rent, hotel,
housekeeping, utilities etc. Would like the formula to check for any of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures. Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key words
(in
some cases multiple key words) to trigger an accounting code number in the
appropriate cell? For instance Account code '3' may include rent, hotel,
housekeeping, utilities etc. Would like the formula to check for any of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

See if this helps:

http://contextures.com/xlFunctions02.html

If you still need help let me know and I'll post an email address where you
can contact me.


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number
column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the
array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column
and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures.
Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key
words
(in
some cases multiple key words) to trigger an accounting code number in
the
appropriate cell? For instance Account code '3' may include rent,
hotel,
housekeeping, utilities etc. Would like the formula to check for any
of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

Have looked at the web link, printed it out and tried to follow it. Maybe I
haven't properly described what I am trying to do because I really do not see
an example that is relevant (at least that I recognize). May be best to
forward a file to you with the 'lookup' table that I have created, see the
columns of the spreadsheet, to make sure that you can see exactly what I am
looking for and let me know if it is possible.

"T. Valko" wrote:

See if this helps:

http://contextures.com/xlFunctions02.html

If you still need help let me know and I'll post an email address where you
can contact me.


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number
column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the
array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column
and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures.
Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key
words
(in
some cases multiple key words) to trigger an accounting code number in
the
appropriate cell? For instance Account code '3' may include rent,
hotel,
housekeeping, utilities etc. Would like the formula to check for any
of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks

Gary

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download.


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 09:19:03 -0800, Gary wrote:

Have looked at the web link, printed it out and tried to follow it. Maybe I
haven't properly described what I am trying to do because I really do not see
an example that is relevant (at least that I recognize). May be best to
forward a file to you with the 'lookup' table that I have created, see the
columns of the spreadsheet, to make sure that you can see exactly what I am
looking for and let me know if it is possible.

"T. Valko" wrote:

See if this helps:

http://contextures.com/xlFunctions02.html

If you still need help let me know and I'll post an email address where you
can contact me.


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I created a lookup table but can't seem to get it to work properly; not
returning accurately.
I go to 'insert function' and select VLOOKUP. For the Lookup_value I am
selection the 'description' column that contains the description of the
entries.
Table_array, I highlight the table that I have created with the associated
codes
Col_index_num, I am a little uncertain about. Have created a number
column
in the table array and selected it, but have tried a number of variants;
number column by itself, included the text part of the
array............not
really sure what should go there.
The 'Range' value, not sure about it either.

If necessary, I could send a sample of a cash sheet

"T. Valko" wrote:

Create a 2 column lookup table with all the key words in the left column
and
the code number in the right column. For example:

.......A......................B
1...rent....................3
2...hotel..................3
3...utilities...............3
4...travel.................5
5...meals................5
6...entertainment.....6
7...golf...................1
8...booze...............1a

Then, you can use a formula like this to return the accounting code:

E1 = travel

=VLOOKUP(E1,A1:B8,2,0)


--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
Working with a cash worksheet that I list deposits and expenditures.
Need
to
add accounting code column with an assigned number dependent on
nature/description of expenditure. Is there a way for certain key
words
(in
some cases multiple key words) to trigger an accounting code number in
the
appropriate cell? For instance Account code '3' may include rent,
hotel,
housekeeping, utilities etc. Would like the formula to check for any
of
those words and put '3' in the cell recording the relevant accounting
code.

Thanks