Multiple workbook VLOOKUP formula

Hi all.
My name is Ryan
New to the forums!

I have a small issue, well I say its small, that I've been pulling my hair out about all evening!

I have two spreadsheets that I need to compare (I have attached both)

IAM2.xls is the master database I need to compare against. WBJ1.xls is the sheet containing the data I need to compare.

I want to compare the data in the ID collumn (D) on sheet WBJ1.xls with the ID data in sheet IAM2.xls (A) and project the results in collumn E on WBJ1.xls. If it finds a match, it tells me where the match has been found and what cell its in on the other sheet. If no match is found I would like it so displayed NOMATCH in bold.

Is this at all possible?

Thanks so much!

Ryan

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Hi stanny2k,

welcome to the forum. Try this in E2 and copy down. Adjust ranges to suit.

=IF(ISNA(MATCH(D2,[IAM2.xls]Sheet1!$A$1:$A$25,0)),"NOMATCH","A"&MATCH(D2,[IAM2.xls]Sheet1!$A$1:$A$25,0))

Thanks so much mate! It works perfect! I've kinda told you the wrong details on the sheets thou. I need them the other way around if possible? I've tried working that out myself but cant get it to work! Keeps on coming up with No Match.

I need the data in collumn A on Master.xls compared to the data in collumn D on Database.xls and projected into the Results collumn F on Master.xls

Please can you take a look at the revised sheets...

Thank you so much! You've saved me hours of headache!

This in F2 on master.xls and copy down

=IF(ISNA(MATCH(A2,[Database.xls]Sheet1!$D$1:$D$500,0)),"NO MATCH","D"&MATCH(A2,[Database.xls]Sheet1!$D$1:$D$500,0))

Thank you so much mate! This works perfectly!

please don't quote whole posts. It's just clutter. In fact, only quote when you are referring to something particular that is not in the previous post, and then only quote the pertinent lines.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Hi again,

Thought I'd reopen a thread that I already talked about, as its pretty much the same issue. Save space etc!

I have this formula, which you very kindly helped me with before...

=IF(ISNA(MATCH(A2,':\05 Implementation\2011 MI\[autolapse.xlsx]Sheet1'!$A$2:$A$3738,0)),"NO MATCH","A"&MATCH(A2,':\05 Implementation\2011 MI\[autolapse.xlsx]Sheet1'!$A$2:$A$3738,0))

I'll break it down so hopefully someone will be able to see where I'm going wrong.

A2 is the number I want to look up from the other Workbook..
:\05 Implementation\2011 MI\[autolapse.xlsx]Sheet1 is the directory where my Workbook is held, where I'm looking to match this number from.
$A$2:$A$3738 is where the possiblilites this number could appear.
"A" is where the original number I'm looking for appears.

I know the number exists in my autolapse workbook as its in Cell 576. Not sure why I'm getting NO MATCH.

Any ideas anyone?

Thanks

You don't specify a drive letter.

':\05 Implementation\2011 MI\[autolapse.xlsx]Sheet1'!$A$2:$A$3738

does not work as a source range. But, for example

'C:\05 Implementation\2011 MI\[autolapse.xlsx]Sheet1'!$A$2:$A$3738

does work fine. Specify the drive letter, otherwise, how is Excel supposed do know which drive to look at?

You need to be aware that Match() returns the position of the match found. So, if you look at range $A$2:$A$3738 and the match is found in cell A2, then Match() will return 1, since A2 is the 1st cell of the range where the match is found. The number returned by Match() is not the same as the row number.

You could change your cell range to $A$1:$A$3738 and then the Match() result and the row number will be identical.

cheers,

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Yes, you can only edit the thread within two days of its creation.

I'll set it to solved.

Have a great New Year!!

mate? did you look at my avatar? I prefer "china plate", LOL! -- Anyway, all the best for 2011!!