Vlookup

[RickCov]

Hi all,

This one may seem like an easy one to most but its causing me some grief none the less!

Here is the scenario... I Need to lookup a cell value in a dataset on another sheet (Book) and return Y if its there, N if it isnt. So here is the formula i typed..

=IF(VLOOKUP(A1,Book!G:G,1,0),"Y","N")

This works fine if the value IS on the other sheet, but it returns #N/A if its not. Does this formula require an ISERROR function?

Could somebody assist me on this as ISERROR is a new one for me.

Thanks & Regards
Richard

[Pete_UK]

Hi Rick,

try it this way:

=IF(ISNA(VLOOKUP(A1,Book!G:G,1,0)),"N","Y")

then it will be compatible with earlier versions of Excel.

Hope this helps.

Pete

[RickCov]

Pete,

This works like a charm, Thank you so much!!

Rick

[Pete_UK]

You're welcome, Rick - thanks for feeding back. You could have used MATCH if you are just looking for the existence in the other sheet:

=IF(ISNA(MATCH(A1,Book!G:G,0)),"N","Y")

or even COUNTIF:

=IF(COUNTIF(Book!G:G,A1)=0,"N","Y")

both of which can execute faster than VLOOKUP.

If you are happy that this has solved your problem, could you mark the thread as Solved? Also, you can click on the "star" icon in the bottom left corner on any post that has helped you.

Pete

[arthurbr]

In your original formula =IF(VLOOKUP(A1,Book!G:G,1,0),"Y","N"), a lookup value of 0 will yield "n" ?

On the other hand =IF(ISNA(VLOOKUP(A1,Book!G:G,1,0)),"N","Y") will yield "y" if the lookup value is 0 ?

Which one do you need?

[RickCov]

Hi Pete,

Ive clicked the star, how do I mark it as solved?

Rick

[Pete_UK]

Hi Rick,

From the FAQs:

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Hope this helps.

Pete

[RickCov]

Pete,

This has now been "Solved"

Rick