Use of Array in IF function to return a "store-type" based on a text fragment from "store name."

[EricVDG]

I downloaded a credit-card summary statement. Each row represents a purchase (including date, store name and amount). A given row of data might look like this:

1/1/12

Publix #246 SR98286659

$128

<tbody>
</tbody>

I want to add a fourth column that automatically qualifies a customized "store-type" (e.g., grocery, hardware, clothes, art-supplies, etc.) based on the name of the store listed in column 2.

Nested IF statements might work, but formulas become cumbersome when there are many stores. Moreover, data in the 'store' field often includes non-essential information, so wildcards will be necessary.

I'm imagining that I will have to use an IF function in an array format which references a separate dynamic list that includes store name (col 1) and store type (col 2) to return a value.

I imagine it working this way: The formula in column 4 looks for a text fragment in column 2 (e.g., "Publix*") and searches a separate dynamic list by store name and returns the associated store-type to column 4.

1/2/12	Publix #246 SR98286659	$130	Grocery
1/3/12	Home Depot #5 208909 dlsf	$100	Hardware
1/4/12	Publix #16 LKO kjnlksld 22335	$80	Grocery

<tbody>
</tbody>

An array formula is the only thing I can think of that is powerful enough to manage such complex data. Suggestions?

 

[Marcelo Branco]

Hi,

Say you have this table in G1:H2

G H

Publix	Grocery
Home depot	Hardware

<tbody>
</tbody>

then put this array formula in D1
=INDEX($H$1:$H$2,MATCH(TRUE,ISNUMBER(SEARCH($G$1:$G$2,B1)),0))

confirmed with Ctrl+Shift+Enter

copy down

M.

 

[T. Valko]

I downloaded a credit-card summary statement. Each row represents a purchase (including date, store name and amount). A given row of data might look like this:

1/1/12

Publix #246 SR98286659

$128

<TBODY>
</TBODY>

I want to add a fourth column that automatically qualifies a customized "store-type" (e.g., grocery, hardware, clothes, art-supplies, etc.) based on the name of the store listed in column 2.

Nested IF statements might work, but formulas become cumbersome when there are many stores. Moreover, data in the 'store' field often includes non-essential information, so wildcards will be necessary.

I'm imagining that I will have to use an IF function in an array format which references a separate dynamic list that includes store name (col 1) and store type (col 2) to return a value.

I imagine it working this way: The formula in column 4 looks for a text fragment in column 2 (e.g., "Publix*") and searches a separate dynamic list by store name and returns the associated store-type to column 4.

1/2/12	Publix #246 SR98286659	$130	Grocery
1/3/12	Home Depot #5 208909 dlsf	$100	Hardware
1/4/12	Publix #16 LKO kjnlksld 22335	$80	Grocery

<TBODY>
</TBODY>

An array formula is the only thing I can think of that is powerful enough to manage such complex data. Suggestions?

One way...

Create a table with the store names in the left column and the type in the right column:

Book1
	F	G
2	Costco	Grocery
3	MyStore	General
4	Publix	Drug
5	Home Depot	Hardware
Sheet1

Book1
	A	B	C	D
2	1/1/2012	Publix #246 SR98286659	128	Drug
3	6/15/2012	POS MyStore 123456	55	General
4	6/15/2012	123xx Costco 56xxx 23	64	Grocery
5	6/15/2012	Home Depot 123456 0002	100	Hardware
Sheet1

This formula entered in D2 and copied down:

=LOOKUP(1E100,SEARCH(F$2:F$5,B2),G$2:G$5)

 

[EricVDG]

Marcelo Branco, Thank you so very much ! This worked really well! Are there limitations I should be aware of? For example, could I make the referencing data (columns G and H in your example) as an expanding table for additional data in the future?

 

[EricVDG]

Biff, this worked really wonderfully !!! Thank you! I've learned so much. I had never used the 1E100 function before. Are there any assumptions or limitations I should be aware of with this construct. Would it be possible to have the "referenced table" (columns F & G in your example) be part of an expanding table so we could add additional stores in the future?

 

[T. Valko]

Biff, this worked really wonderfully !!! Thank you! I've learned so much.

You're welcome!

Are there any assumptions or limitations I should be aware of with this construct.

If the string being searched contains more than one store name that formula will return the strore name that is listed nearest the bottom of the store list table. However, since these are financial transactions I think it is not likely that more than one store name will be in a transaction.

Would it be possible to have the "referenced table" (columns F & G in your example) be part of an expanding table so we could add additional stores in the future?

Sure...

Create these named ranges.

In Excel 2007 or later...

Goto the Formulas tab>Defined Names>Define Name

In Excel 2003 and earlier...

Goto the menu Insert>Name>Define

Name: Store
Refers to:

=Sheet1!$F$2:INDEX(Sheet1!$F$2:$F$50,MATCH("zzzzz",Sheet1!$G$2:$G$50))

Name: Type
Refers to:

=Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$50,MATCH("zzzzz",Sheet1!$G$2:$G$50))

Adjust for a reasonable end of range to allow for added items. I use down to row 50.

Then, the formula in D2 and copied down would become:

=LOOKUP(1E100,SEARCH(Store,B2),Type)

 

[Marcelo Branco]

Marcelo Branco, Thank you so very much ! This worked really well! Are there limitations I should be aware of? For example, could I make the referencing data (columns G and H in your example) as an expanding table for additional data in the future?

Eric,

You are welcome and thanks for the feedback.

No limitations. You can expand the table as needed and adjust the formula accordingly.

M.