Array Matching

  • Thread starter Thread starter rudekid
  • Start date Start date
R

rudekid

Hi

Trying to match an array called countries which, surprisingly, is
list of countries, against a series of columns in a separate workshee
which may or may not contain the correct spelling of one of thes
countries. I then need to return the column reference when or if thi
appears.

I can't find an Excel look-up function that matches arrays with arrays
only a known reference, e.g. a cell, a column, a word or number.

I could do a nested IF using loads of VLookups but figure there must b
a cleverer way. Any ideas???

thank
 
Trying to match an array called countries which, surprisingly, is a
list of countries, against a series of columns in a separate worksheet
which may or may not contain the correct spelling of one of these
countries. I then need to return the column reference when or if this
appears.
Click to expand...
...

MATCH(Array,AnotherArray,0) returns an array of the matching positions within
AnotherArray for each entry in Array. If you only want to check that such
matches exist, try COUNTIF(AnotherRange,Array)>0 - note the change from an
arbitrary array with MATCH to a range with COUNTIF.
 
sorry, hopefully this will help:

On sheet1 I have an array of 250 countries

On sheet 2 I have the address data where the country can appear in an
of the fields in each row because the source data is badly formatted
i.e. though field 8 may say "country" in reality the country coul
appear in field 6, 7, 8, 9 or wherever.

What I want to do is scan each row and find where the country is, usin
the array on sheet1. If I can locate this country, my next aim is t
cut and paste the country to a new location - the same column for ever
row. I also want to do this for towns but I can adapt the code fo
this purpose.

The reason it's difficult is that the lookup functions in Excel rely o
you telling it what to look for in the array but because in thi
instance I'm dealing with 2 arrays, the list of countries and th
address data I'm looking in, I can't do that
 
Back
Top