Return a Result Based on First Word of Cell

Hello. Would anyone be able to help me with a formula that returns a result based on the first word of a cell? I have a formula set up to return results based on five words should they appear in said cell, however, it returns results according to the order my formula lists the the words being searched for. I need it to return results based on the first word of the cell it is searching in. Thanks in advance.

List your five words, together with what you want them to return, in a small table somewhere - suppose this is in X1:Y5. Then you can use this formula:

=VLOOKUP(LEFT(A1,FIND(" ",A1)-1),$X$1:$Y$5,2,0)

assuming your string is in cell A1.

Hope this helps.

Pete

Hi Pete, thank you for your response. I will try this now and hope it works!

Still having a little problem. And maybe it will help if I understand the formula. Your formula says to vertical look up the left of cell A1 and find the first " " (to me this means blank space so I am a little confused about that part), then I don't exactly understand the A1)-1 part. I get the next part which is telling it to look up the information on said table. Then I don't understand what the 2,0 is for.

And, it is six words with six respective returns now, no longer five, I added a category.

I plugged in the formula, created a table (with six not five), and typed in one of the words in the cell it should be searching in, and it returns #value error.

The formula has a slight typo. It should read

Please Login or Register  to view this content.

.

The " " represents the space we're assuming you're using to separate the words in your list. The "2,0" means return the value for the nearest match to the first word in your column. However, VLOOKUPs on TEXT values will not work unless they are an exact match.

Since you've added a word to your list, you'll need to change the range in your VLOOKUP from $X$1:$Y$5 to $X$1:$Y$6.

Hi. Thank you for responding. Yes, I changed the VLOOKUP from five to six. It still returns the error. Help...

Attach your workbook with sample data and explain your desired results, click "Go Advanced" button and find Paperclip Button to attach your file.

Thanks

From your description in Post #4, it seems that you just entered one word in A1 to test it, whereas I thought you would be entering a group of words with spaces in between. For a single word (and/or multiple words), you should make this amendment:

=VLOOKUP(LEFT(A1&" ",FIND(" ",A1&" ")-1),$X$1:$Y$6,2,0)

If you are still having problems, then attach a sample workbook, as others have requested - the FAQ describes how to.

Hope this helps.

Pete

Okay. I sure hope I attached it. It looked like I did but I do not see anywhere here that shows it is attached to this message... So sorry everyone, I am new to this. Please let me know if there is an attachment, I wrote further details on the spreadsheet. Thanks always for all of your help.

Array enter this formula in N3:

Formula:

Please Login or Register  to view this content.

(Array enter means commit the formula with Ctrl + Shift + Enter.)

Then fill down and across.

I don't knowingly upload macro workbooks (*.xlsm files). I uploaded this one before I noticed. The return file is attached as an *.xlsx file.

Does this do what you want?

Hello FlameRetired. Thank you for your help. I should have explained better, the columns my table is in does not have anything to do with the formula I need. I need the formula to sit in cell H3 (all all cells below) and search for the word(s) in cell C3. Column C is the "finding" column, which will sometimes (more than likely) have multiple findings listed. I need it to look at the first word of that cell, see if it is one of the six categories, and return the respective word for said category. I will play with your formula a bit as well, but if you have a quick fix, I would truly appreciate it. Thanks!

Hope this works, pls see the file attach..

regards

Hi Azumi. Oh my gosh I think it is so close. Only thing is, I want it to look for said word at the left most part of the cell (C3), and return a certain word. For instance, it should look up "term" and if it finds that at the furthermost left of cell C3, it should return the word "Review" in H3. So I need it to find "term" and return "Review", find "not" and return "Collect", find "over" and return "Credit Acct", find "class" and return "Adjust", find "adjust" and return "No Action", find "transfer" and return "Offset".

Ooops sorry Im miseed that one:

=IFERROR(LOOKUP(2^15,SEARCH($N$14:$N$19,SUBSTITUTE(SUBSTITUTE(LEFT($C3,SEARCH(" ",$C3,1)-1),"",""),"","")),$O$14:$O$19),LOOKUP(2^15,SEARCH($N$14:$N$19,SUBSTITUTE(SUBSTITUTE($C3,"",""),"","")),$O$14:$O$19))

put in H3 and dragged down

Azumi YOU FREAKING ROCK!!!!! Thank you so much. That worked perfectly. And now, I want to understand the formula and its functions so I can do it myself if ever needed similar in the future. Thank you again. And thank you everyone.

Originally Posted by sillyevi

.......I need it to look at the first word of that cell, see if it is one of the six categories, and return the respective word for said category. I will play with your formula a bit as well, but if you have a quick fix, I would truly appreciate it. Thanks!

I missed the H3 part. The formula(s) still work. Array enter this one in H3 and fill down.

Formula:

Please Login or Register  to view this content.

Hope this works.

Originally Posted by martindwilson

have you tried
=IFERROR(LOOKUP(1,SEARCH($N$14:$N$19,C3),$O$14:$O$19),"no action")

Good one, Martin. Thanks for sharing this one.

Originally Posted by sillyevi

Azumi YOU FREAKING ROCK!!!!! Thank you so much. That worked perfectly. And now, I want to understand the formula and its functions so I can do it myself if ever needed similar in the future. Thank you again. And thank you everyone.

Thanks for you r feedback..

Cheers