Dear Forum,
I have a worksheet with one column which contains alphanumeric data, with spaces etc.
Currently I have a nested IF statement pasted into column B, which searches the respective cell in column A for certain words and depending on these words enters a value into column B.
Below is a sample of my unwieldy IF statement.
IF(ISNUMBER(SEARCH("spade",A2)),"TOOLS",
IF(ISNUMBER(SEARCH("fence",A2)),"FENCING",
IF(ISNUMBER(SEARCH("seat",A2)),"FURNITURE",
IF(ISNUMBER(SEARCH("fork",A2)),"TOOLS",
""
)
)
)
)
If anyone can help to make my IF statement less complicated to manage it would be awesome.
I've been trying to produce a separate worksheet with all the search words and values, but not sure how to get it working. Or maybe my approach is wrong altogether?
Any recommendations well appreciated.
Cheers,
KFCI
Last edited by kfci; 10-18-2011 at 07:21 AM.
Is there a pattern to the alphanumeric data in column A?
Hi quekbc,
There's no real pattern to the data in Column A. The only thing that is consistent is it is alphanumeric and less than 256 chars.Originally Posted by quekbc
Thanks,
KFCI
I put some sample text in cell A1, then in cells E1:E4 I put the words spade, fence, seat and fork and in cells F1:F4 TOOLS, FENCING, FURNITURE and TOOLS.
Then, in B2 the formula
=INDEX(F:F,MATCH(FALSE,INDEX(ISERROR(FIND(E1:E4,A1)),0)))
You should really put that inside an IFERROR statement to handle no match being found
=IFERROR(INDEX(F:F,MATCH(FALSE,INDEX(ISERROR(FIND(E1:E4,A1)),0))),"No match")
How's that for you?
Hi Andrew-R,
Thanks heaps for the response. 99% there and working like a charm in its current form. I have tried to amend the formula to reference a separate sheet for the data, which seems to break.
I created a new sheet called "Category1" and put my reference data in columns A and B. Then updated the formula to reference the new sheet:
Working fine=IFERROR(INDEX(Category1!B:B,MATCH(FALSE,INDEX(ISERROR(FIND(Category1!A1:A54,A4)),0))),"No match")
It would also be awesome if the formula could work on a variable number of rows in the Category1 worksheet, highlighted in the code above. Something like Category1!A:A would be great, but it always throws an error. My working process would be to go through my data and see where there is a "No match" from the formula above and add additional rules into the Category1 worksheet to cover the "No match". Ultimately I will end up with a match for every value from the formula by adding additional matches into the Category1 worksheet.
Whilst I am traditionally a C# programmer, I am finding the analytical abilities of Excel very powerful. This is helping no end!
Thanks,
KFCI
Last edited by kfci; 10-18-2011 at 04:58 AM.
You could try the following - so long as there are no blank rows in column A of the category sheet it should be OK.
=IFERROR(INDEX(Category1!B:B,MATCH(FALSE,INDEX(ISERROR(FIND(INDIRECT"Category1!A1:A" & COUNTA(A:A)),A4)),0))),"No match")
Andrew-R,
Blimey that was quick! Albeit there was an opening bracket missing, it worksAlthough I have found an anomaly, not sure the cause, but using evaluate formula I have found that the MATCH function is producing a false response, when it should be producing a true response.
I can see in the INDEX list that there is a FALSE, which should MATCH, but it fails and so I end up with "No match"? Strange, but do you know what would cause this? The MATCH does however match on some occurrences, which is weird.
Thanks again.
That is strange - Can you post an example workbook where this happens?
Sorry about the missing "(" - that's what I get for (a) falsly assuming I am clever enough to type in complex formula rather than running them in Excel and (b) trying to do anything before the first round of coffee has kicked in
Coffee is an absolute requirement! I can't function without it.
Example workbook attached.
Cheers,
KFCI
Sorry, me being stupid again - I missed a parameter out of the MATCH statement and the default one isn't the one we want ...
=IFERROR(INDEX(Category1!B:B,MATCH(FALSE,INDEX(ISERROR(FIND(INDIRECT("Category1!A1:A" & COUNTA(Category1!A:A)),A2)),0),0)),"No match")
Re: VLOOKUP, SEARCH and IF
Fantastic!!! [SOLVED].=IFERROR(INDEX(Category1!B:B,MATCH(FALSE,INDEX(ISERROR(FIND(INDIRECT("Category1!A1:A" & COUNTA(Category1!A:A)),A2)),0),0)),"No match")
Thanks a million Andrew-R. You're a real life saver and have saved me years of work
Thanks again.
P.S. How do I mark this "officially" as SOLVED?
Happy to help
To mark as solved:
1. Edit your original post in this thread.
2. Click the "Go advanced" button
3. Select "[SOLVED]" from the drop-down list next to the thread title
4. Save changes
job done!happy to help
to mark as solved:
1. Edit your original post in this thread.
2. Click the "go advanced" button
3. Select "[solved]" from the drop-down list next to the thread title
4. Save changes
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks