Extended If+Search, aka Partial Lookup

Good day,

I've checked the other posts here, and haven't seen an answer to my question, but am willing to be redirected to another thread if I've missed something.

I have been using an =IF(ISNUMBER(SEARCH(... function to check the contents of a list I've created. The function I've been using determines if part of the text in a cell matches my 'check'; if it does then a number is inserted in the neighboring cell. For example,

[ A1 ] [ B1 ]
[ABC] [=IF(ISNUMBER(SEARCH("AB",A1)),100,IF(ISNUMBER(SEARCH("DE",A1)),200,...]
[DEF] [ . ]
[HIJ] [ . ]
[ABD] [ . ]
[HIF] [ . ]
[KLM] [ . ]

The problem is that my list has grown over time and I have more than the maximum 7 items for an IF function. I've tried the LOOKUP function, but wildcards don't work, or I'm not implementing them correctly. I can't use an exact search array because there are many combinations of terms in my list.

I'm hopeful that a kind soul can help me out. I appreciate your time, and thanks in advance for your insight.

Best regards,
.

If all the values that you are looking at are 3 charcters long, and the values you are looking for are 2 characters long this might work

Make a table somewhere that is
A;B
AB;100
DE;200
etc.

Then in the formula in column B do the following:

=INDEX(Sheet2!$B$1:$B$100,IF(TYPE(MATCH(MID(A1,1,2),Sheet2!$A$1:$A$100,0))=16,IF(TYPE(MATCH(MID(A1,2,2),Sheet2!$A$1:$A$100,0))=16,0,MATCH(MID(A1,2,2),Sheet2!$A$1:$A$100,0)),MATCH(MID(A1,1,2),Sheet2!$A$1:$A$100,0)),0)

Overly Long I know, but basically you check to see if the first 2 characters are in the list, and if not, then you check the second 2 characters.

I'm sure there has to be an easier way out there though..

Hope that helps though...
John

Thanks, John, for your fast reply. I've gone over what you provided, and I'm not sure that it addresses what I'm looking for.

I have a 2D array (1st column contains ID codes, 2nd column contains dollar amounts); I also have a drop-down list that contains, among other things, the ID codes. I'd like to check for the ID code, and insert the accompanying dollar amount. For example,

ID Codes_Dollar Amounts
123___$100
456___$200
789___$300
etc.

Drop-Down List__Result
123 - Push____$100
123 - Pull____$100
456 - Twist____$200
789 - Twist____$300
etc.

This worked great with the 'if' function until my 2D array got too big.

Do you still feel that the 'index' function you've provided will work? If so, I'll try again to adapt it, but my confidence is not high.

Thanks again for your help.

Best regards,
.