StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If your reference list in in ColumnA of Sheet 2, try <code>=VLOOKUP(D2,Sheet2!A:A,1,FALSE)</code> in <code>E2</code> 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<code>#NAME?</code></p> <p>Edit</p> <p>For T/F results<br> <code>=IFERROR(IF(MATCH(D1,'Atlantic Canada Codes'!A:A,0)>0,"TRUE","FALSE"),"FALSE")</code><br> should work (though something much less inelegant seems possible!)</p> <p><em>Edit re apparent inconsistency in formulae results as mentioned in a comment</em> </p> <ul> <li><code>=TRIM()</code> (worksheet) and <code>Trim</code> (VBA) behave differently. </li> <li><code>=MATCH</code> and <code>=V / H LOOKUP()</code> may behave differently according to whether exact or otherwise.</li> <li>A space character may be ‘normal’ (7-bit ASCII code 32) or non-breaking (&nbsp code 160).</li> </ul> <p>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 <code>=TRIM()</code> to <strong>both</strong> 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.</p> <p>It is possible that the apparent inconsistency mentioned in a comment arose because <code>something+space</code> was not matching to <code>something</code> and that in removing <code>+space</code> from <code>something+space</code> the same was applied to <code>somethingelse+space</code>. Hence where before =TRIM() <code>somethingesle+space</code> was matching with <code>somethingelse+space</code> after <code>somethingelse</code> was no longer matching with <code>somethingelse+space</code>.</p> <p>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.</p> <p><a href="http://office.microsoft.com/en-gb/excel-help/trim-function-HP010062581.aspx" rel="nofollow">Ref:</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload