Help understanding a LOOKUP formula - LOOKUP with a string in a cell

[fab54]

Hi guys,
I've been reading this forum everytime I needed help with excel and you guys are awesome!! ray:

I had a problem when trying to use vlookup but matching a string within a cell.
I came accross this thread in a forum that illustrates the problem (and has a great solution)
http://tinyurl.com/5j6s2n

The questions is:
I've got a spreadsheet of my bank transactions. If the transaction description contains a keyword from another list, I'd like to assign it a category from the next column over.

For example:

Sheet1 has transaction descriptions like:

POS DB WAL-MART # 9100 08/09 1030 HUNTERS CO
THE OLIVE GARD KNOXVILLE TN
POS DB TARGET T20 9100 08/04 4700 NEW HARVES


Sheet2 has a list of keywords and categories like:
Column A Column B
WAL-MART Entertainment - Toys
TARGET Entertainment - Toys
OLIVE GARD Food - Dinner


I was using vlookup but there are problems because many vendors include
the transaction date in the description like the walmart and target
items above.

Any suggestions?

The answer is (the penultime post in the thread)
The list on Sheet2, beginning in cell A1
* no match
wal-mart Entertainment - Toys
target Entertainment - Toys
olive gard Food - Dinner

Note: I added the asterisk item (*) at the TOP of the list.

Then....on Sheet1
A1: POS DB WAL-MART # 9100 08/09 1030 HUNTERS CO

B1:
=LOOKUP(10^99,MATCH("*"&Sheet2!$A$1:$A$10&"*",A1,0)/(Sheet2!$A$1:$A$10<>"")<wbr>,Sheet2!$B$1:$B$10)

Note: That formula is durable against unmatched items (e.g. new accounts).
It returns "no match" in those instances.



Could anyone explain this formula please?
I know the lookup and match formula, but the look_up_values (10^99 and the one in MATCH) look unusual. What's the division for?

I know it's a wird question but i'm trying to improve my understanding of excel, i do't want to just copy paste formulas that work.
Help would be highly appreciated

 

[Gerald Higgins]

The 10^99 part is a short way of writing an extremely large number, and thus is one way of forcing Excel to return the last value in a lookup array.

 

[fab54]

Hi, thanks for the reply,
I dont'undestand the look_up_value in the MATCH function
what does the division symbol mean?

 

[xld]

If there are any blank items in the lookup array, the expression

MATCH("*"&Sheet2!$A$1:$A$10&"*",A1,0)

will match with these as well as the actual items, so the division it used to ignore the blanks, so the

MATCH("*"&Sheet2!$A$1:$A$10&"*",A1,0)/(Sheet2!$A$1:$A$10<>"")#

will create an arry of 1 for matches, #N/A for no match, and #DIV/0 for the blanks.

LOOKUP on the big number then finds the last match item index which it uses to pick the value from the final array, Sheet2!$B$1:$B$10