How to check in another table if an agency is in a specific region

There are many travel agencies each with a code of three characters in a column. I want to know if the travel agency is in Atlantic Canada. I can get a list of all the codes corresponding to travel agencies in Atlantic Canada from a database and put them in a single column in another worksheet in the same Excel file, but I'm not sure of the exact syntax for writing the IF ...LOOKUP or if there is a better Excel formula/technique.

If the codes are in D2:D68 in this example, I want column E (E2:E68) to look up if the value in column D corresponds to one of the lengthy list of codes. Column E would then display yes/no or true/false or something similar.

I've been looking at various tutorials. I don't want to actually look up anything, I just want yes/no or true/false that the three-character code is one of, or not one of, the many Atlantic Canada codes.

Suggestions?

Answers


If your reference list in in ColumnA of Sheet 2, try =VLOOKUP(D2,Sheet2!A:A,1,FALSE) in E2 copied down as required. This could be adapted to show TRUE/FALSE but for identification purposes a code repeat seems adequate. FALSE is to accept a match only when exact (TRUE would allow approximate matches - ie similar but not correct). Ones that do not match will display#NAME?

Edit

For T/F results =IFERROR(IF(MATCH(D1,'Atlantic Canada Codes'!A:A,0)>0,"TRUE","FALSE"),"FALSE") should work (though something much less inelegant seems possible!)

Edit re apparent inconsistency in formulae results as mentioned in a comment

  • =TRIM() (worksheet) and Trim (VBA) behave differently.
  • =MATCH and =V / H LOOKUP() may behave differently according to whether exact or otherwise.
  • A space character may be ‘normal’ (7-bit ASCII code 32) or non-breaking (&nbsp code 160).

Rather than cover all these combinations, a simplified (so far from complete) explanation is that where ‘extra’ spaces are a problem, usually trailing ones, apply =TRIM() to both the data to be looked up (ie the key) and to at least that part of the array (the keys) which relate to the required results.

It is possible that the apparent inconsistency mentioned in a comment arose because something+space was not matching to something and that in removing +space from something+space the same was applied to somethingelse+space. Hence where before =TRIM() somethingesle+space was matching with somethingelse+space after somethingelse was no longer matching with somethingelse+space.

That is the problem was switched from 'surplus' spaces in one list to 'surplus' spaces in the other. Either have these spaces in neither, or if you must, in both.

Ref:


Given your current spreadsheet, you could test the error condition of a VLOOKUP.

=NOT(ISERROR(VLOOKUP(A5,D:D,1,0)))

If there's a convenient way to concatenate the column of values into a single cell of comma-separated values, then you can do a FIND against a single cell. There may be performance advantages (only significant if your spreadsheet is large enough that you sometimes wait for it to recalculate).

=NOT(ISERROR(FIND(A5,D2,1,0)))

Need Your Help

What happens to a thread if FreeOnTerminate=true and an exception is thrown in OnTerminate?

multithreading delphi

Given this example, how do we ensure that the thread instance has been freed? What happens to the thread instance when Thread.FreeOnTerminate=true and you blow up in your Thread.OnTerminate event? ...

Return RGB values from Range.Interior.Color (or any other Color Property)

excel vba excel-vba colors rgb

I was trying to incrementally change the background color of a cell to black, and I found that the Range.Interior.Color method returns a Long which is seemingly arbitrary. Looking at the documenta...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.