VLOOKUP

samsung92

Board Regular
Joined
Sep 15, 2006
Messages
93
Completely new to Excel.
I have 2 columns of data and I want to search the second column to see if any data in the first column is there.
I have been told about VLOOKUP but do not know how it works.
If someone could let me have a formula that would be great.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
What result do you want exactly? A Yes or No somewhere? Or what?
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
=IF(VLOOKUP(Q2,A:B,2,FALSE)=TRUE,"Yes","No")

Replace Q2 with the word you are looking for (cell reference)

A:B is the table of data it is looking in, extend as required

2, is the cell reference

False will give you exact results
 

samsung92

Board Regular
Joined
Sep 15, 2006
Messages
93

ADVERTISEMENT

Does not seem to work?
The data I am trying to look up against is all numbers if that helps.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hi

Try:

=IF(ISNA(VLOOKUP(Q2,A:B,2,0)),"No","Yes")

Regards,
Jon
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

samsung92

See if this is what you want. Formula in C2 (copied down):
=ISNUMBER(MATCH(B2,A:A,0))
Mr Excel.xls
ABCD
1List 1List 2Result
2230FALSE
3apple23TRUE
4B Smith45FALSE
523.56F SmithFALSE
6856appleTRUE
72 o'clock3 o'clockFALSE
814FALSE
989FALSE
10
Check Lists
 

samsung92

Board Regular
Joined
Sep 15, 2006
Messages
93
Many thanks to everyone for your help.
Here is the data to show what I need to do,I want to see how many times the data in column C apperas in column R.

Mat. doc. (GR Date) Pstg date PO Stat Date (N/A not on ZMDP) MvT Time Description Material Plnt SLoc Quantity User Type VS OTD STO.Purch.doc STO OTD
4550043789 ON TIME
5000292052 3-Oct-05 4550039092 30-Sep-05 101 08:18:18 UNC STUDBOLT 041338 2000 FRN 4 DSCARG STO 4550048930 ON TIME
5000292341 3-Oct-05 4550037900 28-Sep-05 101 14:03:18 SETSCREW N/P M12 x 55MM 119612055 2000 FRN 3 DSCARG STO 4550049846 LATE
5000292277 3-Oct-05 4550038459 04-Oct-05 101 13:57:54 REDUCER 334011508 2000 FRN 2 DSCARG STO 4550050192 ON TIME
5000292340 3-Oct-05 4550034700 22-Sep-05 101 14:02:58 RTD SUB-ASSEMBLY THRUST BEARING 3993/7063/712 2000 FRN 1 DSCARG STO 4550050192 ON TIME
5000292346 3-Oct-05 4550034700 22-Sep-05 101 15:04:05 RTD SUB-ASSEMBLY THRUST BEARING 3993/7063/712 2000 FRN 1 DSCARG STO 4550050456 ON TIME
5000292331 3-Oct-05 4550038976 05-Oct-05 101 13:59:10 SEAL TO SUIT SB474/C 64/07226003/16 2000 FRN 8 DSCARG STO 4550050986 ON TIME
5000292051 3-Oct-05 4550036885 02-Sep-05 101 08:17:18 PRESSURE TRANSMITTER - ATEX 64/60001108/16 2000 FRN 1 DSCARG STO 4550051266 ON TIME
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
samsung92

I think you can see that we haven't really got a clue what is in what column. See if this simple example is any help. Formula in D2 (copied down):
=COUNTIF(R:R,C2)
Mr Excel.xls
CDEFGHIJKLMNOPQR
1List 1Count in Col RList 2
2red1yellow
3blue3pink
4yellow1blue
5orange0blue
6green1red
7black
8green
9blue
10
Check Lists
 

samsung92

Board Regular
Joined
Sep 15, 2006
Messages
93
I have now managed to do my first VLOOKUP run.
Many thanks to you all for your help.
What an excellent site :biggrin:
 

Forum statistics

Threads
1,136,589
Messages
5,676,679
Members
419,644
Latest member
KeelsM

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top