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

 

[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.

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).

Sorry for the delayed response, but if I may ask one final question!

In the formula: =IF(ISNA(VLOOKUP(D2;[book2.xlsx]Sheet1!$D$2:$D$11;1;0));"No";"Yes")

Rather than have it say "YES" if the client name is there, is there a way in which I can make it write the name written in cell D2 (and other corresponding cells) instead?

I tried writing =IF(ISNA(VLOOKUP(D2;[book2.xlsx]Sheet1!$D$2:$D$11;1;0));"No";"D2"), but it does not work unfortunately

 

[Jyggalag]

Sorry for the delayed response, but if I may ask one final question!

In the formula: =IF(ISNA(VLOOKUP(D2;[book2.xlsx]Sheet1!$D$2:$D$11;1;0));"No";"Yes")

Rather than have it say "YES" if the client name is there, is there a way in which I can make it write the name written in cell D2 (and other corresponding cells) instead?

I tried writing =IF(ISNA(VLOOKUP(D2;[book2.xlsx]Sheet1!$D$2:$D$11;1;0));"No";"D2"), but it does not work unfortunately

Nevermind, I figured it out, just had to remove the gooseeyes :D

 

[Jyggalag]

But you only joined 2 hours ago

I used this website a long time ago on an old account and I remember Fluff responding to me there haha.

Still a novice at Excel though, returning to working in it after a long hiatus, and very limited experience in coding and functions/formulas