Array Matching

[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

 

[Harlan Grove]

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.

...

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.

 

[rudekid]

went AWOL for a few days and still confused upon return. Might hope if
I post a sort-of-solution:

=ADDRESS(ROW()+1,MATCH("w. country",ImportData!$A$1:$W$1,0),4)

This produces a cell address if it finds the field heading "w.
country". What I need to do now is get a cell address if it finds from
a list of 250 countries, a match in the next row down.

I guess I need to insert a formula where it currently says, "W.
country" which returns the name of the country that matches, e.g.
Germany, so that it can give the cell reference of whatever the country
is it finds. But for that, I need to match 2 ranges against each other
and hence I find myself back with the same problem. Merely finding
whether there is a match isn't good enough: I need the address of the
match.

thanks

 

[Alan Beban]

Your description is very confusing. Perhaps brief illustration would be
helpful, a la, e.g.:

In A1:A3 of Sheet1 I have France, England,Germany, respectively.
On Sheet 2 in A15 I have the names of various countries.
I would like to return the Sheet2 address references for all occurrences
of "France".

Or whatever you really want.

Alan Beban

 

[rudekid]

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