Advanced lookup formula

[rich872]

Alright so I have two excel spreadsheets, Book 1 with name, userID and license field columns all populated. The other, Book 2 with just name and userID colums populated. I need to find a way to take the userID in Book 2, see if it exists in Book 1, it if does then return whatever value is in the license field. Whats the easiest way to accomplish this? Thank you in advance.

[sandy666]

try PowerQuery

PowerQuery1
PowerQuery2

[rich872]

so no way to do this with a built in lookup function?

[alansidman]

I'm thinking you might be able to do this with Vlookup or Index/match. It would be helpful if you posted sample workbooks.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[sandy666]

so no way to do this with a built in lookup function?

You've Ex2016 so PowerQuery is built-in

[rich872]

Ok, I've attached two sample workbooks.

[leelnich]

Hi all- Try this INDEX/MATCH lookup formula. Named Ranges: UserID = [Book1.xlsx]Sheet1!$B$2:$B$5 License = [Book1.xlsx]Sheet1!$C$2:$C$5
Both workbooks should be open when defining the names (in Book2) (Name Manager on the Formulas tab) . Then Book1 can be closed.

Please Login or Register  to view this content.

[MarvinP]

In Book2 in C2 put this formula and pull it down:

Please Login or Register  to view this content.

[rich872]

YES!! That did it! Thanks so much leelnich!!!