Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search and lookup
Heres my situation: In a cell Id like to develo a nested function where I can
search for a string( 8-character serial number) listed in an adjacent cell, and return a TRUE, FALSE, or custom message depending on whether it finds the value or not. In my case, the database values I am searching through are all listed in one column, 15,000 rows deep, and are not sorted in ascending order. I'm using this as a failsafe to determine if the string (which is the serial number) actually exists (to make sure the user has entered the serial number correctly) so that the item can be crossed off the list. I also have VBA code to highlight the item corresponded with the serial number once the failsafe function has determined the serial number is correct... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search and lookup
Unless I'm missing something here it looks to me as if
you could use one of several tests, but MATCH will work as well as any. Setup: A1 = where you're typing in the serial number to verify B1 = where our formula will go Z1:Z15000 the list of valid serial numbers in B1 put this =IF(ISNA(MATCH(A1,Z$1:Z$15000,0)),"Boom! - No Match","Good Number!") "KJW31" wrote: Heres my situation: In a cell Id like to develo a nested function where I can search for a string( 8-character serial number) listed in an adjacent cell, and return a TRUE, FALSE, or custom message depending on whether it finds the value or not. In my case, the database values I am searching through are all listed in one column, 15,000 rows deep, and are not sorted in ascending order. I'm using this as a failsafe to determine if the string (which is the serial number) actually exists (to make sure the user has entered the serial number correctly) so that the item can be crossed off the list. I also have VBA code to highlight the item corresponded with the serial number once the failsafe function has determined the serial number is correct... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP w/ multiple search columns? | Excel Discussion (Misc queries) | |||
Data Lookup using find and filtered dropdowns | Excel Discussion (Misc queries) | |||
search all tabs for tab name specified & lookup reference? | Excel Worksheet Functions | |||
Lookup returns #NA when search value (text) has leading zeros. | Excel Worksheet Functions | |||
Lookup with search range start based on position of last blank lin | Excel Worksheet Functions |