Identify value of ordered list within a cell

[bm661]

I have two cells (A1 and B1) which consist of lists where the values are separated by commas. I would like to be able to enter a term found in B1 and pull its corresponding value from A1.

For example

A1	B1	C1	D1
San Jose, Paris, Nova Scotia, Tokyo	Costa Rica, France, Canada, Japan	Canada	Nova Scotia

<tbody>
</tbody>

In C1 I can enter Canada and D1 will return Nova Scotia. If there are multiple Canadas within B1 it is fine to pull the first corresponding instance. The number of items in column A will always equal the number of items in column B.

With
=SUBSTITUTE(MID(SUBSTITUTE("," & A1&REPT(" ",6),",",REPT(",",255)),E1*255,255),",","")

where E1 = 3 since I know Canada is the third item in the list. Would there be a way with a formula to determine Canada's position within that list?

I would like to avoid text to columns since the number of items within each lists varies and I would not know how many columns to allocate in the rest of the sheet. I would also prefer not to use VBA. Thank you!

 

[Momentman]

Something like this

Excel 2010 32 bit
	A	B	C	D	E
1	San Jose, Paris, Nova Scotia, Tokyo, Abuja, Brussels, Moscow	Costa Rica, France, Canada, Japan, Nigeria, Belgium, Russia	Belgium	Brussels	6
Sheet4

Cell Formulas
Range	Formula
D1	=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",90)),1+90*(E1-1),90))
E1	=ROUNDUP(SEARCH(C1,SUBSTITUTE(B1,",",REPT(" ",90)))/90,0)

 

[bm661]

Genius! Thank you so much!