How do I write a basic VLOOKUP statement?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Maureen - 24 Sep 2007 22:48 GMT

I know most of the posts here are for advanced users... but, please help!
I have a column of data in one spreadsheet (column A) in a workbook and
another column of data (also column A) in another spreadsheet in the same
workbook. I want to confirm that all of the data in the first spreadsheet is
in the second, and identity those that are not.

I know this is a very very basic VLOOKUP statement, but I am lost.

thanks for anyones help,

Reply to this Message

Peo Sjoblom - 24 Sep 2007 22:52 GMT

It's not a vlookup job, look here

http://www.cpearson.com/excel/Duplicates.aspx

Signature

Regards,

Peo Sjoblom

>I know most of the posts here are for advanced users... but, please help!
> I have a column of data in one spreadsheet (column A) in a workbook and
[quoted text clipped - 6 lines]
>
> thanks for anyones help,

Reply to this Message

Maureen - 24 Sep 2007 23:06 GMT

Great link, thanks for the quick response.

> It's not a vlookup job, look here
>
[quoted text clipped - 10 lines]
> >
> > thanks for anyones help,

Reply to this Message

Elkar - 24 Sep 2007 23:04 GMT

Give this a try. In column B of Sheet 1 enter:

=IF(ISNA(VLOOKUP(A1,'Sheet 2'!$A$1:$A$100,1,FALSE)),"Missing","OK")

Copy down Column B for each value in column A. I just assumed your data was
in cells A1 thru A100. Change this range to meet your needs. Also, change
Sheet 2 to match the actual name of your second sheet.

HTH,
Elkar

> I know most of the posts here are for advanced users... but, please help!
> I have a column of data in one spreadsheet (column A) in a workbook and
[quoted text clipped - 5 lines]
>
> thanks for anyones help,

Reply to this Message

Maureen - 24 Sep 2007 23:08 GMT

thank you very much, I've felt like an idiot for the last couple of days
trying to "self educate myself and get it to work. You have ended my
frustration!

> Give this a try. In column B of Sheet 1 enter:
>
[quoted text clipped - 16 lines]
> >
> > thanks for anyones help,

Reply to this Message

Rod - 25 Sep 2007 08:46 GMT

for future reference, remember that ISNA only works for the one type of
error. you can use ISERROR in the same fashion for all errors not just
missing data. if you only need to find the missing data, and don't need to
validate correct data you can use
=IFERROR(VLOOKUP(A1,'Sheet 2'!$A$1:$A$100,1,FALSE)),"Missing")
and this will work just for finding errors in your range without having to
validate any other data.

> thank you very much, I've felt like an idiot for the last couple of days
> trying to "self educate myself and get it to work. You have ended my
[quoted text clipped - 20 lines]
> > >
> > > thanks for anyones help,

Reply to this Message

JE McGimpsey - 25 Sep 2007 13:19 GMT

Note that IFERROR was introduced in XL07.

> if you only need to find the missing data, and don't need to
> validate correct data you can use
> =IFERROR(VLOOKUP(A1,'Sheet 2'!$A$1:$A$100,1,FALSE)),"Missing")
> and this will work just for finding errors in your range without having to
> validate any other data.

Reply to this Message

Peo Sjoblom - 25 Sep 2007 15:22 GMT

That's why one wants to use it, if you don't want to get NA errors because
the value cannot be found it's perfect to use that particular function to
trap it but using ISERROR or IFERROR (in Excel 2007) will trap all errors
even those that can be of interest to know

Signature

Regards,

Peo Sjoblom

> for future reference, remember that ISNA only works for the one type of
> error. you can use ISERROR in the same fashion for all errors not just
[quoted text clipped - 35 lines]
>> > >
>> > > thanks for anyones help,

Reply to this Message