Excel 2007 : Nested Search Formula

**Broken85** · 02-07-2011, 12:26 PM

Hello All,

I am working on a formula to search a cell and based on 3 possible findings insert one of 3 possible responses in another cell. I am not very good with VBA and would like to stay in a formula if possible.

The cell i am searching will have one of three, three letter codes randomly spaced in the cell. (ie Doe, John F STF Annapolis MD 2344; Scott, Michael T MGR Tampa FL 1558, or Johnson, Bob E CTR Baltimore, MD 3399) It will be either STF, MGR, or CTR.

I would like to put the value corisponding to the three letter code in another cell.

Any ideas would be greatly welcome.

Thanks,
Kenn

**NBVC** · 02-07-2011, 12:29 PM

What is the value you are putting in the "other" cell?

**DonkeyOte** · 02-07-2011, 12:40 PM

If we assume string is A1

Please Login or Register  to view this content.

modify the result_vector responses as desired

**Broken85** · 02-07-2011, 12:57 PM

NBVC
The value that would be filled into the new cell would be either STF,MGR, or CTR.

Donkey
what is the 9.99E+307 for in your code snippet.

**DonkeyOte** · 02-07-2011, 01:00 PM

It is a big number 9.99*10^307

Used in this context it ensures LOOKUP returns the value from the result_vector associated with the last numeric value found in the lookup_vector

Brief overview: http://www.excelforum.com/2466052-post12.html - though this is aimed at the text equivalent the same principles hold true for this numeric version

**Broken85** · 02-07-2011, 01:23 PM

Cool thanks for the knowldege and the formula works like a charm. Can i add criteria to it just by adding another search definition and response?

**DonkeyOte** · 02-07-2011, 01:32 PM

Yes, if you have lots of items it would probably make more sense to store them (and responses) in a range and alter the formula accordingly, eg:

Please Login or Register  to view this content.

where C1:C10 hold search terms (* in C1) and D1:D10 associated responses (="" in D1)

the use of INDEX & REPT is just to account for possibility that C1:C10 may contain blanks (to be ignored)