Excel 2007 : Nested Search Formula

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

=LOOKUP(9.99E+307,SEARCH(INDEX(C1:C10&REPT("ZZZ",C1:C10=""),0),A1&" "),D1:D10)

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)