Add different values in a column to another list of values

[anafilipe]

Hi,

I would like your help. I usually need to match specific values from one column to a list in another. Usually I do it manually by copy pasting, but would love to know how to do it with a formula.

A simple example is:


What formula can I use to do this?

Thanks

 

[Chihiro]

Something like below?
=CHOOSE(COUNTIF($A$1:A2,A2),$D$2,$D$3,$D$4)

Assuming each set of Text1~Text6 will have distinct match. You will need to extend formula as needed.
4th set will need something like...
=CHOOSE(COUNTIF($A$1:A2,A2),$D$2,$D$3,$D$4,$D$5)

Or you can set it up with index# for list of matches and use something like...
=INDEX($F$2:$F$8,MATCH(COUNTIF($A$1:A2,A2),$E$2:$E$8,0))

See attached.

 

[Chihiro]

Oh wait. You can shorten second version to something like. No need for index#.
=INDEX($F$2:$F$8,COUNTIF($A$1:$A2,A2))

To make it dynamic use named range.


Formula becomes...
=INDEX(lstMatch,COUNTIF($A$1:$A2,A2))

 

[anafilipe]

Hi Chihiro,

Thank you very much for your quick answer.

I tried your formulas and really like that -
=CHOOSE(COUNTIF($A$1:A2,A2),$D$2,$D$3,$D$4) and =INDEX(lstMatch,COUNTIF($A$1:$A2,A2))

I'm sure I'm going to use them a lot.

Now, what do you recommend when we have the 2 lists as unique values and we want to have the 2 columns as mentioned before? The formulas you mention work when the one of the lists have the right rows already. So, when I have the 2 unique lists and want to match them and create the equivalent columns, what is possible to do? The results don't need to have a specific order as long as the combination of list 1 to all elements of list 2 (or other way around) are there.

Thanks!

 

[Chihiro]

In that case, there's 2 ways of doing it.

1. Formula method. See link for details.
http://www.mrexcel.com/forum/excel-...ble-combinations-two-lists-without-macro.html

2. Use MS Query to produce Cartesian Product Join of 2 list

My preferred method is 2. There's few steps involved here.

1. Create 2 sheets in the workbook. Each containing 1 of the list and covert the list to table. Lets call each sheet List1 and List2. Save the file.

2. Go to Data tab in ribbon tool and find "Get External Data"->"From Other Sources"->"From Microsoft Query"

3. Choose "Excel Files" and click ok.

4. Navigate and select the file saved in step 1 and hit ok.

5. You should see something like below. If not, click on Options and check "System Tables".


6. Expand List1$ & List2$ and move over columns (list to match 1, list to match 2) over to right. Then hit "Cancel".

7. Popup window will appear, click "Yes".

8. You should see table populated. Click on "File" and "Return Data to Microsoft Excel".

9. Choose where you want to return the data. See attached for end result.

 

[anafilipe]

Hi Chihiro,

This works perfectly and saves a lot of time.

Thank you very much!

Ana