# 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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### 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
33
Views
713
Replies
9
Views
216
Replies
3
Views
135
Replies
1
Views
265
Replies
18
Views
322

### Forum statistics

1,141,153
Messages
5,704,607
Members
421,359
Latest member
Edwardvanschothorst

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back