[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Look up table not working

Posted on 2013-05-29
16
Medium Priority
?
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 200 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 200 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 1600 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

650 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