Excel vlookup problems

[Jyggalag]

Dear all,

I am relatively new to Excel and have little experience with functions, especially across different excel files.

I currently have two excel-files, where one looks like this (file 1):

And the other one looks like this (file 2):

Currently, what I would like is for file 1 to return the value "YES" to every cell in column K. However, I ONLY want it to do this, if the client in column D in the corresponding cell number in file 1 is also present as a client in column D in file 2.

Is there some nice way to do this for example through a vlookup? And how would I go about it?

Any assistant would be greatly appreciated! Thank you so very much everybody!

Kind regards,
Jyggalag

 

[jasonb75]

Welcome to Mr Excel

There are many ways that you could do this, I would say that either MATCH or COUNTIF would be the simplest methods.

=IF(ISNUMBER(MATCH(D2,[Book2]Sheet1!$D$2:$D$11,0)),"YES","NO")

=IF(COUNTIF([Book2]Sheet1!$D$2:$D$11,D2),"YES","NO")

Countif would be my personal choice, but that will not work if 'file 2' is closed.

 

[Jyggalag]

Welcome to Mr Excel

There are many ways that you could do this, I would say that either MATCH or COUNTIF would be the simplest methods.

=IF(ISNUMBER(MATCH(D2,[Book2]Sheet1!$D$2:$D$11,0)),"YES","NO")

=IF(COUNTIF([Book2]Sheet1!$D$2:$D$11,D2),"YES","NO")

Countif would be my personal choice, but that will not work if 'file 2' is closed.

Thank you so much Jason!

I can see that you refer to book2 and sheet1 in these codes.

Can I just enter this as a function in excel or would I need to use VBA codes?

 

[Phuoc]

Or try this:

=IF(ISNA(VLOOKUP(D2,[Book2.xlsx]Sheet1'!$D$2:$D$40,1,0)),"No","Yes")

 

[Jyggalag]

H

Welcome to Mr Excel

There are many ways that you could do this, I would say that either MATCH or COUNTIF would be the simplest methods.

=IF(ISNUMBER(MATCH(D2,[Book2]Sheet1!$D$2:$D$11,0)),"YES","NO")

=IF(COUNTIF([Book2]Sheet1!$D$2:$D$11,D2),"YES","NO")

Countif would be my personal choice, but that will not work if 'file 2' is closed.

Hello Jason,

Currently if I do it I get this error (please note that I have to write ; instead of , when making formulas):

 

[Jyggalag]

S

Or try this:

=IF(ISNA(VLOOKUP(D2,[Book2.xlsx]Sheet1'!$D$2:$D$40,1,0)),"No","Yes")

Same error here I feel like it's getting close tho.

 

[Fluff]

You need to include the single quotes like

=IF(ISNA(VLOOKUP(D2,'[Book2.xlsx]Sheet1'!$D$2:$D$40,1,0)),"No","Yes")

You also need to change Book2.xlsx & Sheet1 to the name of your workbook & sheet

 

[jasonb75]

Currently if I do it I get this error (please note that I have to write ; instead of , when making formulas):

You need to change all of the commas to semicolons, you've only done the last 2

=IF(ISNUMBER(MATCH(D2;[Book2]Sheet1!$D$2:$D$11;0));"YES";"NO")

Phuoc's formula is missing an apostrophe

=IF(ISNA(VLOOKUP(D2'[Book2.xlsx]Sheet1'!$D$2:$D$40'1'0))'"No"'"Yes")

Other than that both should work fine.

edit:- one point to note, I haven't used the file extension (.xlsx) with my suggestion. In the event that you have 2 workbooks open with the same name and different file extensions then you could encounter problems there (never actually tried it).

Also, if 'Book2' is not open then you would need to include the entire file path in the formula (gets messy), so it is better to do it with the file open, excel will then add the rest of the path for you' Just remember that if you move or rename the file then the formula will break (this applies to any formula that refers to a different workbook, not just mine).

 

[Jyggalag]

You need to include the single quotes like

=IF(ISNA(VLOOKUP(D2,'[Book2.xlsx]Sheet1'!$D$2:$D$40,1,0)),"No","Yes")

You also need to change Book2.xlsx & Sheet1 to the name of your workbook & sheet

You need to change all of the commas to semicolons, you've only done the last 2

=IF(ISNUMBER(MATCH(D2;[Book2]Sheet1!$D$2:$D$11;0));"YES";"NO")

Phuoc's formula is missing an apostrophe

=IF(ISNA(VLOOKUP(D2'[Book2.xlsx]Sheet1'!$D$2:$D$40'1'0))'"No"'"Yes")

Other than that both should work fine.

Fluff I remember you, you're literally a legend on this website. Very honored to have you visit my thread here

Thank you so incredibly much guys. Truly! Both of you!

I fixed it and it works now!! :D

I cannot express my graditude, you're helping me so much here! Thank you!

I apologize for being way inferior compared to you in Excel. I really appreciated your patience here as well

Have a wonderful monday!

Kind regards,
Jyggalag

 

[jasonb75]

Fluff I remember you, you're literally a legend on this website.

But you only joined 2 hours ago