Solved

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

Posted on 2014-01-29
6
141 Views
Last Modified: 2014-02-11
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.

Image of Excel Books 1, 2 and 3.
Thanks for your help!
0
Comment
Question by:LessonsLearned
  • 3
  • 2
6 Comments
 

Author Comment

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

Expert Comment

by:Dreamboat
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.

Cascading: Ken Puls

http://www.excel-user.com/2011/02/cascading-validation-lists.html

Conditional: Debra Dalgleish

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

Accepted Solution

by:
Angelp1ay earned 500 total points
ID: 39819405
In B2:
=VLOOKUP(A2,[Book2.xlsx]Sheet1!$A$1:$C$5,3,FALSE)

Open in new window

In C2:
=VLOOKUP(B2,[Book3.xlsx]Sheet1!$A$1:$C$5,3,FALSE)

Open in new window

Book1.xlsx
Book2.xlsx
Book3.xlsx
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 11

Expert Comment

by:Angelp1ay
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)

Open in new window

0
 

Author Closing Comment

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

Expert Comment

by:Angelp1ay
ID: 39850167
No problem.

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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

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

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

Join & Ask a Question