You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- Thread starter anafilipe
- Start date

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.

=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.

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!

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!

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.

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.