x
Solved

# In Excel, how can I look up a value and return the results

Posted on 2014-01-29
Medium Priority
153 Views
Scenario:
I have 3 spreadsheets
Book 1 with Worksheet "Main"
Book 2 with Worksheet "FindBox"
Book 3 with Worksheet "Find6Digits"

In Book 1, I want to look-up the contents of cell A2 ("25"), in Book 2 (array column A), and return a 6 digit code (from column C) with the result being placed in Book 1, cell B2.

In Book 1, I want to look-up the contents of cell B2 ("257820"), in Book 3 (array column A), and return an "In" or "Out" result (from column D) that will be placed in Book 1, cell C2.

See image below.

Thanks for your help!
0
Question by:LessonsLearned
• 3
• 2

Author Comment

ID: 39819330
Note:  All lookups have to be "exact" match or return a "N/A" or "Not Found".
0

LVL 22

Expert Comment

ID: 39819392
Though I don't know how to do it myself off the top of my head, I believe you're looking for what is called "cascading vlookup" or "conditional vlookup", tho they are the same thing, I believe.

Conditional: Debra Dalgleish

http://blog.contextures.com/archives/2013/09/26/conditional-drop-down-lists-in-excel/
0

LVL 11

Accepted Solution

Angelp1ay earned 2000 total points
ID: 39819405
In B2:
``````=VLOOKUP(A2,[Book2.xlsx]Sheet1!\$A\$1:\$C\$5,3,FALSE)
``````
In C2:
``````=VLOOKUP(B2,[Book3.xlsx]Sheet1!\$A\$1:\$C\$5,3,FALSE)
``````
Book1.xlsx
Book2.xlsx
Book3.xlsx
0

LVL 11

Expert Comment

ID: 39819413
For reference, if the workbook is in a different folder, you can do stuff like this:
``````=VLOOKUP("a",'C:\SomeFolder\MyExcels\[2014 Example.xls]Data'!\$B\$2:\$E\$4,3,False)
``````
0

Author Closing Comment

ID: 39848888
I apologize for not responding sooner.  The solution worked GREAT!
0

LVL 11

Expert Comment

ID: 39850167
No problem.

Very nicely posed question! Please come back soon :)
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
###### Suggested Courses
Course of the Month4 days, 19 hours left to enroll

#### 579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.