# Data Look Up and return

#### Rich4rd

##### New Member
Hi all,

I was hoping you could help with the following problem. I’ve been trying to use “VLOOKUP” but must be doing something wrong or more likely, using the wrong formula completely!

In column A I have a long list of 6 digit numbers (7000+), in column B I have another, much shorter list of 6 digit numbers. If any of the numbers in column B are in column A I would like a return of ‘YES’ next to the number in column C.

Many thanks for any help!

Richard

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### NeonRedSharpie

##### Well-known Member
You don't really need a vlookup for this. You can, but that formula would be like this:

Code:
``=IFERROR(IF(VLOOKUP(\$B1,\$A:\$A,1,0)=B1,"Yes",""),"")``

A little cleaner code would be:

Code:
``=IF(COUNTIF(\$A:\$A,\$B3)>0, "Yes","")``

#### Special-K99

##### Well-known Member
in C1
=IF(NOT(ISERR(VLOOKUP(B1,A\$1:A\$7000,0))),"Yes","No")
and copy down column C

#### misrasomendra

##### Board Regular
Hi

Try below formula

=IF(ISNUMBER(MATCH(B1,\$A\$1:\$A\$8,0)),"Yes","No")

 123456 678901 Yes 234567 841893 No 345678 458986 No 678901 123456 Yes 790012 888613 No 967756 No 114550 No 132324 No

<tbody>
</tbody>

#### Rich4rd

##### New Member
Thank you all for the quick replies! Seems to have done the job!

Replies
6
Views
315
Replies
3
Views
243
Replies
1
Views
202
Replies
3
Views
505
Replies
3
Views
202

1,195,858
Messages
6,011,979
Members
441,661
Latest member
Pammie007

### 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