So I have a cell with multiple words in A1. I want to check for the word "red" in A1 and if red exists return a value of "red" in B2, IF "red" is not found then search for "blue", if "blue" exists return a value of "blue" in B2, if "blue" is not found is not found then search for "green", if "green" exists then return "green" in B2, if none exist then return value of "none"
Create a list of your keywords in say G1:G3
=IFERROR(LOOKUP(2^15,SEARCH(G1:G3,A1),G1:G3),"none")
G1 = red
G2 = blue
G3 = green
Hope that helps.
Let me rephrase the problem
I have a cell with multiple words in A1. I want to check for the word "red" in A1 and if red exists return a value of "red XL" in B2, IF "red" is not found then search for "blue", if "blue" exists return a value of "blue Medium" in B2, if "blue" is not found is not found then search for "green", if "green" exists then return "Big Green in the house" in B2, if none exist then return value of "none"
The values returned won't be identical to the values searched for.
With
A1: some text...My red apple looks a little green
E1:E3 containing this list:
Red
Blue
Green
This regular formula returns the first matching value from A1...if no matches, then: None
In the above example, that formula returns: Red
Please Login or Register to view this content.
Is that something you can work with?
Allow me to rephrase the answer...
put your list of keywords in say G1:G3
And put your list of results for each keyowrd in H1:H3
G1 = Red, H1 = red XL
G2 = Blue, H2 = blue Medium
G3 = Green, H3 = Big Green
=IFERROR(LOOKUP(2^15,SEARCH(G1:G3,A1),H1:H3),"none")
Try this:
E1:F3 contains this list:
This regular formula returns the phrase associated with the first matching color in A1Please Login or Register to view this content.
Is that something you can work with?Please Login or Register to view this content.
Ah very nice ok, I get it so I should create some lists lol. For organizational reasons I will put the lists on different worksheet now how does that fit in to this equation if they are on a different worksheet?
You can add sheet references to the formula
=IFERROR(LOOKUP(2^15,SEARCH('sheet with list'!G1:G3,A1),'sheet with list'!H1:H3),"none")
A couple notes.
1 there cannot be any blanks in G1:G3
2 if A1 has 2 or more of the key words, the formula returns the one that appears last (furthest down) in the list.
So with Ron's text string
My red apple looks a little green
If you want to return Red XL and not Big Green, then Red should be in G3 instead of G1
3 and finally, it is NOT case sensitive.
if G1 = red, then it will consider RED a match.
To make it case sensitive, change search to FIND.
Last edited by Jonmo1; 06-09-2014 at 01:10 PM.
Ok awesome thanks a million I will give it a shot.
great solution. Just had quick question, what does 2^15 do here?
The maximum possible number of characters in a cell in Excel 2010 is 32767. Hence, this is also the maximum possible return for the SEARCH function. Since 2^15=32768, this ensures that the LOOKUP formula will always return the last possible match.
Regards
2^15 is 2 to the power of 15 = 32768
That just happens to be 1 larger than the maximum lenth text string a cell can hold.
So it assures the lookup value is larger than any possible result of Search.
But, but, but, you have to use BigNum.Originally Posted by XOR LX
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Why is that?But, but, but, you have to use BigNum.
I've always wondered why myself. Ask the people that always use it.
You did just use it (or at least imply that it should be used), So I'm asking you.
But, but, but, you have to use BigNum.
And you got my answer.
Some people just like to use named constants.
Others use them strategically.
Yet, others don't use them at all.
I guess I fall into that 3rd category (mostly).
If I know the nature of the data I'll use the smallest "BigNum" that will work.
Yeah, unless the constant will be used in various places in the workbook...or if it's a rate that I want to clearly identify (Tax_Rate)...I just embed the constant in the formula. So, I'm probably a "use them strategically...but, sparingly" type. :\
This is the key for me.
From the point of view as a helper on this forum, it's not possible for me to 'know' the nature of the data.
Thefore I take the position of being absolutely certain regardless of the nature of the data.
To answer your question Why no BigNum in this formula..
2^15 (as you said) is the smallest BigNum that I know will work.
Because we do 'know' with aboslute certainty that Search/Find can't possibly return a number equal or larger than 2^15
In other formulas, such as finding the last number in a column, we (as in helpers on this forum) can't possibly know with absolute certainty, so I choose Bignum.
Sure it is.
Sometimes OP's will actually give us the real info we need.
Date gives us enough info to know the nature of the data.I have a column of dates. I'd like a formula to return the last date in the column.
And the OP is never wrong (or mistaken to be fair)...right?
Quickly....without googling or using Excel...
What is the last date (furthest in the future) that Excel can use?
And what is the corresponding serial # that you would put in a lookup formula?
BigNumber is too much typing for me. :|
I typically use either:
10^10 which is 10 billion.
or
10^99 (which I don't think has a name)
Here's bignum
9.99999999999999E+307
Largest year 9999. And I didn't (have to) look!
Ok sorry got caught up in meetings. I need it so the values don't change on the list worksheet.
=IFERROR(LOOKUP(2^15,SEARCH('sheet with list'!G1:G3,A1),'sheet with list'!H1:H3),"none")
so when I drag the formula down the spread sheet it doesn't do this =IFERROR(LOOKUP(2^15,SEARCH('sheet with list'!G2:G4,A2),'sheet with list'!H2:H),"none")
Maybe this:
=IFERROR(LOOKUP(35000,SEARCH('sheet with list'!G$1:G$3,A1),'sheet with list'!H$1:H$3),"none")
In this case I use 35000 as the lookup value.
It's greater than the max number of characters allowed in a cell, 32767, and it's a constant (doesn't need to be calculated).
It only seems to look for and return the last values on the lists any ideas
So I have two lists each with two values it appears to only be looking for the 2nd value any thoughts
Can you post a SMALL sample file and show us what result(s) you expect?
SMALL = no more than 20 rows worth of data
I highlighted the columns I want to use this function for.sample.xlsx
I don't see any highlighted cells.
Sorry, but I can't make heads or tails out of your file.
Last edited by Tony Valko; 06-09-2014 at 08:30 PM.
I just left my office. I will send a new file in an hour.
As I said in post # 8
You have to write your list with the keyword priorities sorted Ascending
The keywords with the highest priority go last (at the bottom)
So with your given example of Red Blue and Green
.
With those rules as written,
G1=Green
G2=Blue
G3=Red
Last edited by Jonmo1; 06-09-2014 at 09:27 PM.
HelpSheet.xlsx
So on worksheet "19" Column I has the formula. It is looking for items in list A1 on worksheet "19 Attributes" in column E on worksheet "19". So in row 4 it should return a value but instead is returning the value of "none".
You have extra spaces on 19 attributes that you need to remove them, just add with TRIM:
IFERROR(LOOKUP(2^15,FIND(TRIM('19 Attributes'!A$2:A$3),E2),'19 Attributes'!B$2:B$3),"none")
Last edited by azumi; 06-10-2014 at 04:15 AM.
But, but, but, you have to use BigNum.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks