VLOOKUP variation?

[JChandler22]

I think this should be an easy task for most of you. We'll see...

pleaseHelp!.zip

If you take a look at the attached file, you'll see a list of names. Beside each name is a number that corresponds to that person's country.

On a separate sheet, say sheet2, I want to make a list of all the people from Ireland, for instance, by pulling it from Sheet 1.

My first instinct was a VLOOKUP function, something like:

=VLOOKUP(1,$B$3:$C$13,-1,0)

but as I've found out, VLOOKUP only works from left to right, not the opposite (I would switch the columns, but my actual worksheet is a lot more complex, and won't allow me to do so). Also, I think VLOOKUP would return a list of the same name - whoever is closest to the top.

If anyone can help me tackle this one, I would appreciate it! Many thanks.

[NBVC]

See attached.

Formula in Sheet2!C1: =COUNTIF(Sheet1!C:C,B1) -- This counts matches.

Formula in Sheet2!A3:

Please Login or Register  to view this content.

Confirmed wiht Ctrl+Shift+Enter not just Enter.. Adjust ranges to suit and reconfirm with CSE combo. Then copy down as far as needed to extract all possible matches.... -- This extracts matches.

[JChandler22]

Wow. Perfect. More complicated than I thought! I tried it out with my data and it all worked out.

I really appreciate your help. Thank you.