How to check part of a string from a field and enter a corresponding value in another field

[Countani]

Hello all,
This is my first post in this forum and I am looking for your assistance.

The problem statement.
I have a table which has 2 columns namely Items and Groups

Items would comprise of (Egg, Milk, Bread, Chiken... Diesel, Tyre, Lights) for which the corresponding group would be (Grocery, Grocery, Grocery, Grocery.... Vehicle, Vehicle, Vehicle). There are other items and group, but for the sake of this discussion, I am limiting it to just these.

Items and filled in by hand in column A and what I'd like to do is the automatically enter the Group in column B.
It gets slightly trickier because in column A, I could have Egg 40 nos or Egg 25 nos as inputs, so I'd require a formula that would look for the string "Egg" and then enter Grocery in column B.

I have spent 2 days looking at various formulae such as VLOOKUP, MATCH, ISNUMBER, FIND.... but so far I have fallen well short.

Please advice how to crack this, any help would be appreciated.

Thank you

 

[Akashwani]

Hi and welcome to MrExcel.

IF Egg, Tyre etc is ALWAYS going to be the FIRST word you type then the formula in C2 will work, however, IF Egg, tyre etc could be the first, second or any other word in the cell, then the formula in B2 will work.

Sample data and example results...

Excel Workbook
	A	B	C	D	E	F	G
1	Item	Group	Group		Item	Group
2	Egg 40	Grocery	Grocery		Egg	Grocery
3	Fried Chicken	Grocery	Invalid Entry		Milk	Grocery
4	Tyre 50	Vehicle	Vehicle		Bread	Grocery
5	Back Lights	Vehicle	Invalid Entry		Chicken	Grocery
6	Front Break	Invalid Entry	Invalid Entry		Diesel	Vehicle
7					Tyre	Vehicle
8					Lights	Vehicle
9
Sheet5

Item is a Named Range for the list of Items in column E.
The formula in B2 needs entering with ctrl shift enter NOT just enter, it can then be copied down.
You will need to change the cell references to suit your layout.

I hope this helps.

Ak

 

[Countani]

Hi AK
Thank you for your assistance.
I do have a small problem though, I am not sure where I am going wrong.
I created a list and named it Item and basically did what you had advised.


But when I check, then I notice this -
1) If I enter egg in A2.. and if Egg is the 1st item in the list, then it gives me correct group.
2) But if I enter say milk in A2.. where as the first item in the list is say egg, then it says invalid entry.

Item	Group	Group		Item	Group
egg	Grocery	Grocery		Egg	Grocery
milk	Grocery	Invalid Entry		Milk	Grocery
milk	Invalid Entry	Vehicle		Bread	Grocery
chicken	Grocery	Invalid Entry		Chicken	Grocery
diesel	Grocery	Invalid Entry		Diesel	Vehicle

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

Please advice where I am making the mistake..

Thank you

 

[Akashwani]

Hi,

Have you entered the formula in B2 using ctrl shift enter and NOT just enter?

IF you are going to be entering Egg, Milk etc as the first word, the use the formula in C2 in my first reply.

Based on your sample data above, here's my results...

Excel Workbook
	A	B	C	D	E	F	G
1	Item	Group	Group		Item	Group
2	Egg	Grocery	Grocery		Egg	Grocery
3	Milk	Grocery	Grocery		Milk	Grocery
4	Milk	Grocery	Grocery		Bread	Grocery
5	Chicken	Grocery	Grocery		Chicken	Grocery
6	Diesel	Vehicle	Vehicle		Diesel	Vehicle
7					Tyre	Vehicle
8					Lights	Vehicle
9
Sheet5

The formula in B2 MUST be entered using ctrl shift enter NOT just enter.

Ak

 

[Countani]

Hi AK
Thanks for your note.
I copied the table the you have put, copied the formula into B2 to see how it works.
I have not even gone into my actual excel - wanted to get my head around this formula first before I went into my Excel.

Would copying your formula into B2 using the simple copy option work?

(Might sound funny to all the experts there, but how can I enter anything into a cell by pressing Ctrl Shift and enter?)

Even when I copy the formula into B2, it gives me the problem as stated in my previous post.

Thanks once again...

 

[Akashwani]

Hi,

You can copy and paste my formula.
Click cell B2, in the formula bar paste the formula then press ctrl shift enter, this tells Excel that it is an array formula and Excel places curly brackets around the formula {} you CANNOT enter these brackets yourself to create and array.

You may want to take a look here...

Introducing array formulas in Excel - Excel - Office.com

I hope that helps.

Ak

 

[Countani]

Hi AK
Thank you very much for your assistance.
I spent 2 hours trying to understand the formula from every angle, but it is beyond me.
What I did manage to do was to copy what you wrote and make changes to it so that it works for me - and yes, it did! Shows how good your formula was!

Thanks once again!

Regards

 

[Akashwani]

Hi,

I'm pleased it has worked for you and thanks for your feedback.

Take a look here...

Excel 2007 or 2003 Magic Trick 801: Partial Text Lookup Formula To Return Multiple Records - YouTube

Mike explains Excel formulas better than anyone else and he has loads of excellent videos that will help you with just about every Excel formula.

Ak

 

[Countani]

Hello Ak, all
It is me again.
Let me start off by thanking you for your help the last time. I thought I had it covered and the first few tries did go well, but now it is clear that I just didn't get it.
The table I have is very similar to what you have, and accurate value is displayed in the first cell based on the match by your formula. But when I copy your formula to the subsequent cells below, it gives me the error "invalid entry". I am also using {}.
Any help would be appreciated.
Thanks
Ani

 

[Akashwani]

Hi,

Without seeing an example of your layout and the formula, all I can suggest is that the Item is not listed in column E (or whichever column you are using) or that it is spelt differently in column A (or whichever column you are using)

Ak