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
<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!
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!