View Full Version : Substitute values in a col of 1 sheet using values from another sheet

2017-03-13, 02:31 PM
Hi Guys,

I need to get the output(col B of sheet 1) looking at the input (Col A of sheet 1) with the help of the mapping table sheet.
Is this possible?

Thanks in advance

2017-03-21, 11:19 PM
Is this possible?

I noticed no one has responded on this, primarily (I think) because it doesn't yield any obvious way to solve it with a formula. If this is to complete a one off or infrequent exercise, I would suggest using VBA, as this doesn't have the restrictions that mqke it difficult with a formula.
If I understand correctly, In Sheet1, you have text strings in Col A and you want to build the references for these in Col B. This is done by looking up the numbers against each location in the mapping table and concatenating them into a single string, delimited by a colon if there are multiple locations in Sheet1!A.

If it would work, the problem would be much simplified if your mapping table became Sheet1!A and !B?
Then, if I have UK,India,Kenya in Sheet1!A2, I can look this up in Col A of mapping table and return the correct code from Col B?
A lot depends on how many combinations of locations your going to encounter. I would think that you could build a large table without too much work if you start with the locations in separate columns. You can then look up the location codes (in separate cells) and then concatenate the columns into the two required. I haven't done this because without any inkling of what you intend to do with it, Ive absolutely no idea if it has any mileage... :)