VLOOKUP, SEARCH and IF

**kfci** · 10-14-2011, 06:06 AM

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

**quekbc** · 10-14-2011, 06:28 AM

Is there a pattern to the alphanumeric data in column A?

**kfci** · 10-14-2011, 09:18 AM

Hi quekbc,

Originally Posted by quekbc

Is there a pattern to the alphanumeric data in column A?

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.

Thanks,
KFCI

**Andrew-R** · 10-14-2011, 09:25 AM

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?

**kfci** · 10-18-2011, 04:52 AM

Originally Posted by Andrew-R

=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:

Please Login or Register  to view this content.

Working fine

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

**Andrew-R** · 10-18-2011, 05:05 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.

Please Login or Register  to view this content.

**kfci** · 10-18-2011, 05:35 AM

Originally Posted by Andrew-R

You could try the following - so long as there are no blank rows in column A of the category sheet it should be OK.

Please Login or Register  to view this content.

Andrew-R,

Blimey that was quick! Albeit there was an opening bracket missing, it works

Although 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.

**Andrew-R** · 10-18-2011, 05:48 AM

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

**kfci** · 10-18-2011, 06:01 AM

Coffee is an absolute requirement! I can't function without it.

Example workbook attached.

Cheers,
KFCI

**Andrew-R** · 10-18-2011, 06:22 AM

Sorry, me being stupid again - I missed a parameter out of the MATCH statement and the default one isn't the one we want ...

Please Login or Register  to view this content.

**kfci** · 10-18-2011, 07:12 AM

Please Login or Register  to view this content.

Fantastic!!! [SOLVED].

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?

**Andrew-R** · 10-18-2011, 07:16 AM

Originally Posted by kfci

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

**kfci** · 10-18-2011, 07:21 AM

Originally Posted by andrew-r

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!

VLOOKUP, SEARCH and IF

LinkBack

Thread Tools

Rate This Thread

Display

VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Re: VLOOKUP, SEARCH and IF

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions