# Lookup?

#### MarkAndrews

##### Well-known Member
Fropm this list - I need to confirm if the numbers in column D appear in column A, result in E
HAMMONDS - FA.xls
ABCDE
2159956MrReubenStevens144482
3170908MrRobertMullett174653
4173722MrsIreneNicholson219833
5174653MrsLisaPark245826
6175150MissLeanneWheater288328
7181242MrCraigNewman311779
8187837MrsCatherinePayne313089
9188463MrStephenHunt331670
Sheet1

Whats the best way? Vlookup?

TIA

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### texasalynn

##### Well-known Member
vlookup will do the job

#### njimack

##### Well-known Member
VLOOKUP would work but COUNTIF is simpler.

Code:
``=COUNTIF(A:A,D2)``

#### MarkAndrews

##### Well-known Member
=VLOOKUP(D2,A:A,2,FALSE)

?

I tried the above and it resulted in N/A

HAMMONDS - FA.xls
ABCDE
2159956MrReubenStevens144482#N/A
3170908MrRobertMullett174653#N/A
4173722MrsIreneNicholson219833#N/A
5174653MrsLisaPark245826#N/A
6175150MissLeanneWheater288328#N/A
7181242MrCraigNewman311779#N/A
8187837MrsCatherinePayne313089#N/A
Sheet1

#### njimack

##### Well-known Member

=vlookup(d1,a:a,1,false)

?

I tried the above and it reculted in N/A

Of course it did, D1 contains the word Reference!

#### texasalynn

##### Well-known Member
works for me
Book1
ABCDE
2159956Mr Reuben Stevens144482144482
3170908Mr Robert Mullett174653174653
4173722Mrs Irene Nicholson219833#N/A
5174653Mrs Lisa Park245826#N/A
6175150Miss Leanne Wheater288328#N/A
7181242Mr Craig Newman311779#N/A
8187837Mrs Catherine Payne313089#N/A
9188463Mr Stephen Hunt331670#N/A
Sheet1

#### ExcelChampion

##### Well-known Member

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

#### MarkAndrews

##### Well-known Member
Texaslynn

I mirrored your example & Mine isn't working
HAMMONDS - FA.xls
ABCDE
2159956MrReubenStevens144482#N/A
3170908MrRobertMullett174653#N/A
4173722MrsIreneNicholson219833#N/A
5174653MrsLisaPark245826#N/A
6175150MissLeanneWheater288328#N/A
7181242MrCraigNewman311779#N/A
8187837MrsCatherinePayne313089#N/A
Sheet1

#### ExcelChampion

##### Well-known Member
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.

#### MarkAndrews

##### Well-known Member
VLOOKUP would work but COUNTIF is simpler.

Code:
``=COUNTIF(A:A,D2)``

You could be right Neil

Replies
9
Views
51
Replies
5
Views
60
Replies
8
Views
166
Replies
2
Views
56
Replies
3
Views
99