Match more than one entry

[HWL]

Okay what I have are 2 lists. In list #1 I have a country code (such as US, MX, FR) and in list #2 I have other data. I can use the country code in list #1 to match list #2 to return the data.

I understand how to return the first match it finds but I want to be able to dragdown a column in list #1 to display the multiple (all really) matches in list #2. I know this will require ctrl-shift-enter somehow but I need help.

Thanks

 

[T. Valko]

Okay what I have are 2 lists. In list #1 I have a country code (such as US, MX, FR) and in list #2 I have other data. I can use the country code in list #1 to match list #2 to return the data.

I understand how to return the first match it finds but I want to be able to dragdown a column in list #1 to display the multiple (all really) matches in list #2. I know this will require ctrl-shift-enter somehow but I need help.

Thanks

See if this is what you had in mind:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[gmaboing]

Here's how I'd do it, but there may be an easier way to do this

COLUMN A...........COLUMN B...........COLUMN C..............ANSWER
AUT......................ABA...................1,886....................14,000
XXM......................ABM...................3,772....................#N/A
BAL.......................ABN..................16,29......................#N/A
CBA.......................ABS...................8,200......................49
XYZ.......................AUT..................14,000....................#N/A

FORMULA USED: VLOOKUP(A1,B1:C5,2,FALSE)

Explanation: I'm looking for the value in A1 (AUT) in columns B and C. If the value in A1 (AUT) is found in column B, then pull the corresponding value in column C (14,000) and look for an exact match.

gmaboing

 

[HWL]

See if this is what you had in mind:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

Ah yes, this appears to be exactly what I was looking for. Now let me get it to work how I need. Thanks much

 

[HWL]

See if this is what you had in mind:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

Hey, how about instead of returning the exact value, I want to return the row number, this way I can use it to pick up data in other columns? Does that make sense?

 

[T. Valko]

Hey, how about instead of returning the exact value, I want to return the row number, this way I can use it to pick up data in other columns? Does that make sense?

For just the row numbers...

Based on the formula in that post.

Array entered**:

=IF(ROWS(F$2:F2)>E$2,"",SMALL(IF(Item=D$2,ROW(Item)),ROWS(F$2:F2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[gmaboing]

Hey, how about instead of returning the exact value, I want to return the row number, this way I can use it to pick up data in other columns? Does that make sense?

I thought of:
In cell G1 type =MATCH(A1,C:C,0)

Explanation:
If cell A1's contents exist in column C, return the row number that has the same value of A1 (exact match) in cell G1 <which is where you typed the formula

 

[HWL]

For just the row numbers...

Based on the formula in that post.

Array entered**:

=IF(ROWS(F$2:F2)>E$2,"",SMALL(IF(Item=D$2,ROW(Item)),ROWS(F$2:F2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Okay one more issue...back to the original code, can I force it to display alphabetized?

 

[T. Valko]

Okay one more issue...back to the original code, can I force it to display alphabetized?

Hmmm...

I'm guessing it would be VERY difficult to do with formulas but I can't know for sure unless I can see some sample data with an explanation of what you're wanting to do.

 

[HWL]

For just the row numbers...

Based on the formula in that post.

Array entered**:

=IF(ROWS(F$2:F2)>E$2,"",SMALL(IF(Item=D$2,ROW(Item)),ROWS(F$2:F2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Okay I'm all most there but powers that be put a bug in what I'm trying to do. I'm okay as long as I am matching country pair to country pair but there are actually 3 criteria.

ct-to-ct (which Valko solved)
all-to-ct
ct-to-all

I tried an OR statement but the problem is if one of the critera isn't met it breaks with a #NUM issue.
Am I making sense?