Find + if + lookup

[rockiesmagicnumber]

I'm sorting through a list of business executives and trying to classify them by their titles to sort them into functional areas, but I'm ending up having to use just a giant nested IF statement, and there has to be a better way of doing it.

Not everyone has a uniform title, so I'm using FIND to look at the title and find particular search strings. Basically it's this, over and over again:

=IF(ISNUMBER(FIND("Operation",A1)),"Operations/Manufacturing",

Where the title is obviously in A1. I can easily assemble a VLOOKUP table, but I don't know how to combine this with the ISNUMBER(FIND aspect of this.

Can anybody help?

[khamilton]

Hi Rockies
Can you upload an attachment? If so I think you will find a quick solution or at least get you on the right track.

[NBVC]

IF you have the lookup values in a table then you can use something like this:

=LOOKUP(2,1/(ISNUMBER(SEARCH($C$1:$C$3,A1))),$D$1:$D$3)

where C1:C3 contains words to search for within A1, and D1:D3 contain phrases to return.

[rockiesmagicnumber]

Wow, that simple? I'll give it a shot!

I'll respond soon, thanks for the help

[rockiesmagicnumber]

Alright, I seem to have myself lost again. I tried the lookup, and it's just spitting back zeroes.

[khamilton]

Is this something close?
rockies.xls

[rockiesmagicnumber]

Wow, that works pretty well.

So I know, what does the 2^15 part of the function do?

[khamilton]

its one more than the maximum amount of characters that you can enter in a cell.

[rockiesmagicnumber]

Thanks so much for your help.

[DonkeyOte]

rockiesmagicnumber,

FYI: NBVC's version generates the same results as khamilton's you just need to correct your range references to match your data (as khamilton did in their version), ie:

Please Login or Register  to view this content.

in your sample you're including A73 which is blank - this value will thus always be found in any string you compare it to (An) hence the LOOKUP simply returns contents of Lookup!B73 - which given that was blank also meant you got 0
your reference to A1 should also have been A2 etc

[rockiesmagicnumber]

My mistakes aside, what is the difference between the "2,1/(ISNUMBER..." and the "2^15,SEARCH(..."

[DonkeyOte]

Both are doing the same thing - I guess you could argue the 2^15 will be more efficient given it conducts fewer operations - I was just making the point that the concept worked - you hadn't done anything wrong per se other than include some blanks etc..

To explain LOOKUP in more depth requires a bit of time (I don't have it presently) but in essence the key points are that the function:

- treats the contents of the lookup_vector as though they are always sorted in ascending order (regardless of reality)

- ignores value in the lookup_vector that are not of the same data type as the criteria and importantly this includes error values

- given point 1 - when used with unsorted data if you ensure the criteria exceeds all possible values in the lookup_vector it will yield the last value of the same type within the lookup_vector

- where you have a result_vector the function will return the value from the result_vector that is associated with the lookup_vector result

So in khamiltons example the criteria is specified as a number and a number that will be bigger than anything the SEARCH will generate...
On that basis the function will return the result_vector value associated with the last number found in the lookup_vector

In NBVCs example the lookup_vector is populated with 1 of 2 values: 1 or #DIV/0
Why these 2 values ?
Well because the ISNUMBER(SEARCH(..)) test will only generate one of two responses TRUE or FALSE which when coerced equate to 1 and 0 respectively.... the coercion here takes place by means of the 1/boolean
Thus it follows that 1/1 is 1 and 1/0 is #DIV/0!
By setting the criteria to 2 it thus follows that again the criteria exceeds all possible values within the lookup_vector and thus again the function will return the value from the result_vector associated with the last number returned in the lookup_vector

Hope that helps... (?)

[rockiesmagicnumber]

Yeah, this definitely does help. I'm looking to purchase a book that can go a bit more in depth on stuff like this so that I'm not bothering folks on a message board about this, can anyone recommend something that would go beyond the basics?

[DonkeyOte]

I don't have any book advice I'm afraid but I would say that you're not wasting people's time - the above isn't widely known and the question is asked more often than you might think in fact I would say anyone who does know has probably asked at some point or other be it here or on other similar forum(s) etc...