Lookup Function with Comma Separated Inputs in One Cell

[Nasdaq18k]

Hi,

I need help performing a lookup function with inputs in one cell separated by commas.

I have a single cell with a given number of initials (my lookup values):

RY, AH, NR

I have a table with a cost assigned to each of these initials (my lookup array):

RY 200
AH 200
NR 400

I would like to perform a vlookup off the cell containing the comma separated values to generate an output such as 200, 200, 400.

I would also like the function to be flexible enough to provide outputs based on any given number of inputs in the cell such as
RY, AH, NR, WG or
RY, WG

Thank you so much for anyone's help on this!

 

[Domenic]

Here are a couple of options. The first one returns the results in separate cells. The second one returns the results in a single cell with the use of a free add-in...

Assumptions:

A2:B4 contains the data

D2 contains the input

Formulas:

[Option 1]

E2:

=SUMPRODUCT(--ISNUMBER(SEARCH(A2:A4&",",D2&",")))

F2, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=IF(COLUMNS($F2:F2)<=$E2,INDEX($B$2:$B$4,SMALL(IF(ISNUMBER(SEARCH($A$2:$A$4&",",$D2&",")),ROW($A$2:$A$4)-ROW($A$2)+1),COLUMNS($F2:F2))),"")

[Option 2]

First download and install the free add-in Morefunc.xll. Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUBSTITUTE(MCONCATE(IF(ISNUMBER(SEARCH($A$2:$A$4&",",$D2&",")),", "&$B$2:$B$4,"")),", ","",1)

 

[Nasdaq18k]

Thank you so much for this. I want to try option 2, but I am having trouble downloading the morefunc.xll add-in.

I get an error that says:

Warning: main(./common/init.php) [function.main]: failed to open stream: No such file or directory in /mnt/108/sdc/2/4/xcell05/fclicksql/fclick.php on line 16

Fatal error: main() [function.require]: Failed opening required './common/init.php' (include_path='/mnt/108/sdc/2/4/xcell05/include:.:/usr/php4/lib/php') in /mnt/108/sdc/2/4/xcell05/fclicksql/fclick.php on line 16

Thanks again!
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Domenic]

Try this alternate download site...

http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

 

[Nasdaq18k]

Thank you!