Search A Cell And Output A Result From An Array

**JonnyBoy333** · 09-29-2011, 06:28 PM

Is there a way to create a function that would search a cell for an array of text strings, if it found one of them it would output the corresponding result from a different array of text strings?

Example:
Cell A1: "bag 4 c"
Array 1: "1","2","3","4"
Array 2: "small", "medium", "large", "extra large"
Formula: IF(A1 contains {"1","2","3","4"}, display {"small", "medium", "large", "extra large"}, 0)
Result: extra large

I think a combination of the IF, SEARCH and LOOKUP functions might accomplish this. Any ideas?

**khamilton** · 09-29-2011, 10:16 PM

enter this with control+shift+enter

Please Login or Register  to view this content.

**JonnyBoy333** · 09-30-2011, 03:32 PM

Wow, that is an impressive formula, and it works great, +reputation! The only thing is that I over simplified my example a bit because I am not always trying to look up numbers, and sometimes the order of the information is not consistent or some information is absent, in which case the formula breaks down. Here is a better example:

A1: SHORTS SM BLK
A2: CAP YEL
A3: GLOVE BLU LG

In column B I would like to display the size (where SM=Small, MD=Medium, LG=Large, XL=Extra Large) and in column C I would like to display the color (where BLK=Black, WHT=White, YEL=Yellow, BLU=Blue). The forumula needs to find the right text string in the cell and output its corresponding value. I'm sure the same formula could be used in both cases just with modified constraints. What do you think?

**daddylonglegs** · 09-30-2011, 03:40 PM

Try this formula in B1

=LOOKUP(2^15,SEARCH({"SM","MD","LG","XL"},A1),{"Small","Medium","Large","Extra Large"})

or simpler to name the lists and use

=LOOKUP(2^15,SEARCH(AbSizes,A1),Sizes)

That'll give an error if none of those are found. If you have Excel 2007 use IFERROR to return a blank

=IFERROR(LOOKUP(2^15,SEARCH(AbSizes,A1),Sizes),"")

**JonnyBoy333** · 09-30-2011, 04:12 PM

Yep, that does the trick. Thank you a ton.

Question: How do you create a referencable list exactly? I think that would be useful.

Also, what does the 2^15 signify? For curiosity's sake.

Edit: I have Excel 2010

**khamilton** · 09-30-2011, 08:32 PM

2^15 is one more than the maximum number of characters you can enter in a cell, so it will search the cell and account for the entire number of characters. HTH

**klica** · 10-03-2011, 08:24 AM

Hello can someone help me pls.
Here is idea:
i need to get a value from table but i need to use vlookup and hlookup at once.
Pls download the following xls table.
http://www.megaupload.com/?d=5IMMZ2MD
In the sheet 'Calculate', in columns A and B are the input data; in columns D and E are data that I need to get from sheet 'table';

I was doing before with commands IF and VLOOKUP (if b=15 than vlookup...; if B=20;vlookup ... and so on)
but now i have planty columns to do on that way