Lookup?

[MarkAndrews]

Fropm this list - I need to confirm if the numbers in column D appear in column A, result in E

HAMMONDS - FA.xls
	A	B	C	D	E
1	144482	MrDavidMead		Reference
2	159956	MrReubenStevens		144482
3	170908	MrRobertMullett		174653
4	173722	MrsIreneNicholson		219833
5	174653	MrsLisaPark		245826
6	175150	MissLeanneWheater		288328
7	181242	MrCraigNewman		311779
8	187837	MrsCatherinePayne		313089
9	188463	MrStephenHunt		331670
Sheet1

Whats the best way? Vlookup?

TIA

 

[ExcelChampion]

Countif takes more memory.

 

[texasalynn]

you quickly fix problems where numbers are text by typing 1 in a blank cell, then copy the one. Highlight the data that is text and paste special, multiple, click ok.

 

[ExcelChampion]

Make sure that the reference numbers are formatted the same...meaning, one set might be text while the other a true number.

Copy a blank cell, select one set of reference numbers, and then Pastespecial/values/add. Do the same for the other set.

I already suggested this...and there is no need to type a 1. Copying a blank cell and "adding" it will produce the same result.

Edit: Don't forget to make sure that the formatting of the columns are General. Or, if you want the reference numbers to be text (which they probably should be) then for each set in a blank column use the formula:

=A1&"" (specify the column of your reference numbers)

Then paste the values back into the appropriate columns.

If you do this then make sure to set the formatting of your columns to TEXT.

 

[texasalynn]

I already suggested this...and there is no need to type a 1. Copying a blank cell and "adding" it will produce the same result.

ahh.... so we have a case of not trying all suggestions :wink:

 

[ExcelChampion]

Yep, and quite early in the thread too!

VLOOKUP (or even just LOOKUP) really is the way to go with this. VLOOKUP will use less memory because once it finds a match it will stop. COUNTIF on the other hand will search all the way to the end of the range.

Plus, COUNTIF will see "0123" and "123" as the same thing when in fact they are quite different.

 

[MarkAndrews]

Sorry Guys, I've been busy doing what i needed with the results

All sorted & thanks for all help

I can always get the help i need from this board & its well appreciated