Solved

Look up table not working

Posted on 2013-05-29
16
188 Views
Last Modified: 2013-05-30
Hi all,

I asked a similar question before here regarding lookup tables. But I guess I didn't completely "get it".

Because I'm doing another excel sheet referencing a lookup table and it's not working.

Attached is my botched excel file snippet, with sample data.

Any suggestions on how to fix this.  I promise this time I will finally "get" the lookup table concept.  :)

BTW this is the formula I am trying to adapt

=IFERROR(VLOOKUP(B2,Table1,FALSE),"Not Found")

Open in new window


What I am trying to do is pull in the First Name {Column1)based on Property Street Number and Name (Column2)

Thanks!

Rowby
redoing-lookup-1-file.xlsx
0
Comment
Question by:Rowby Goren
16 Comments
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 50 total points
Comment Utility
You're missing the column number in your vlookup (should be the third parameter). In addition, for your vlookup to work, you need to match the first column of a range and return something to the right of that.

With the setup you have, you need either to move address to the left of name, or use an index / match structure, as in:
=INDEX('Lookup table'!$A$2:$A$5,MATCH(B3,'Lookup table'!$B$2:$B$5,0))

Thomas
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 50 total points
Comment Utility
Will be good if you show expected result.
For B2 cell you should use this:
=IFERROR(VLOOKUP(A2,Table1,2,FALSE),"Not Found")
0
 
LVL 9

Author Comment

by:Rowby Goren
Comment Utility
Hi als315,

Thanks for helping.

Here's a file with the expected results.  Let me know if your formula will work with the attached file.

Rowby
redoing-expected-results.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
als315's formula is correct for the attached file.

you can also add check for blank in column A, so that no result returned if there is no lookup value...

=IF(A2="","",IFERROR(VLOOKUP(A2,Table1,2,FALSE),"Not Found"))
0
 
LVL 9

Author Comment

by:Rowby Goren
Comment Utility
Now I am using the formula on the actual spreadsheet.

So I have to slightly modify the formula because the column on the main page (not the Table1) page is different.

What does the =IF(A2= refer to.   The look up table column or the main page column.

Also what does the second A2 refer to -- the main page A2 or the look up table column A2?)

Thanks!

Rowby
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
The A2 in both instances it the cell that contains the item you want to lookup in the table.

A2 is in the main page (where you are putting the formula).
0
 
LVL 9

Author Comment

by:Rowby Goren
Comment Utility
Sorry for my confusion.  But doesn't the lookup table need the street address in order to link the first name to the last name.

Here's yet another version of my excel file, Missing the formula -- because I think I have my version of the formula all screwed up.  This version matches my actual main page of my excel file -- with dummy data put in.
exerts-exchange-confused.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
I am confused as to what you are trying to accomplish now.

Exactly which cells are to contain the formula you are looking for?

What exactly is being looked up and returned?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 9

Author Comment

by:Rowby Goren
Comment Utility
I have a spreadsheet that when it was created the first name was not included. Only the last name.  So I am trying to fix the spreadsheet by adding the missing first name.

I want the first name looked up based on the street address

I want Column "i" which is headed "Missing First Name" to get that first name based on the street address (which is Column K)

So I assume the lookup table needs to include a column with a list of street addresses and the corresponding first name.

:)

Thanks

Rowby
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
The street address is not in the lookup table, so how will you lookup the last name and street address in the look up table?
0
 
LVL 9

Author Comment

by:Rowby Goren
Comment Utility
Ah, you're right.

HEre's a modified version of the file.  With street address in the lookup table.
exerts-exchange-confused-added-a.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Ok, we'll need an alternative to VLOOKUP since the lookup column is to the right of the return items column....

try INDEX/MATCH

e.g.

=INDEX(Table1[[#All],[First Name]],MATCH(K2,Table1[[#All],[Street address]],0))

copied down.

or

=INDEX(A:A,MATCH(K2,B:B,0))
0
 
LVL 9

Author Comment

by:Rowby Goren
Comment Utility
Hi  NB_VC

I tried adding both formulas to the file, but no result.  Please look at the attached.  I included one of your formulas.

I'm sure we are "getting there"! :)

Thanks

Rowby
exerts-exchange-added-address-to.xlsx
0
 
LVL 23

Accepted Solution

by:
NBVC earned 400 total points
Comment Utility
You entered the formula in the cell twice!

See attached.
Copy-of-exerts-exchange-added-ad.xlsx
0
 
LVL 9

Author Comment

by:Rowby Goren
Comment Utility
Great, NB_VC!

Working.  Now I'm going to apply it to my actual file and award the points (will be accepting multiple solution.)

ROwby
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
Comment Utility
Thanks NB_VC and  nutsch and als315!

NB_VC example spreadsheet did the trick!

Rowby
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now