Solved

Look up table not working

Posted on 2013-05-29
16
214 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
ID: 39205499
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 40

Assisted Solution

by:als315
als315 earned 50 total points
ID: 39205513
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
ID: 39205584
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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 23

Expert Comment

by:NBVC
ID: 39205662
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
ID: 39205717
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
ID: 39205786
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
ID: 39206058
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
ID: 39206458
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39206480
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
ID: 39206586
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
ID: 39206616
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
ID: 39207387
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
ID: 39207838
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
ID: 39207856
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
ID: 39208556
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
ID: 39209078
Thanks NB_VC and  nutsch and als315!

NB_VC example spreadsheet did the trick!

Rowby
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associateā€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

828 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